package TTraker;

# use strict;

sub BEGIN
{
	use Exporter();
	@ISA = qw(Exporter);
	our @EXPORT = qw(
	$author
	$user_name
	$user_password
	$sql_server
	$db_name
	$minTime
	$back_loc	
	$cgi_ref
	$js_ref
	$css_ref
	$log
	$banner
	$images
	initialize_dbi 
	print_log
	run_statement 
	print_header
	log_info
	log_in_screen_from_pm
	csr_activity_screen_from_pm
	csr_work_screen_from_pm
	get_hot_list
	get_hot_list_other
	add_to_hot_list
	drop_from_hot_list
	clear_hold_jobs
	pay_activity_screen_from_pm
	adm_activity_screen_from_pm
	sup_activity_screen_from_pm
	activity_screen_from_pm
	batch_job_select_from_pm
	add_hold_to_batch
	clear_batch_jobs_from_hold
	open_report_screen
	open_report_daily_screen
	start_job_screen_from_pm
	close_window
	parse_input
	get_employee_count
	get_employee_info
	get_log_in_count
	log_in_get_status
	time_stamp_compare
	get_payroll_count
	get_payroll_days
	get_payroll_dates
	get_closed_payroll_dates
	get_payroll_dates_incl_today
	close_payroll_dates
	open_payroll_dates
	get_payroll_periods
	create_payroll_event
	get_current_work_list
	log_employee_in
	calc_elapsed_day_time 
	calc_elapsed_minutes 
	calc_elapsed_seconds
	update_view_temp_tasks 
	update_task_std_time 
	get_task_list
	adm_new_cli_task_verify
	adm_new_master_task_verify
	adm_add_new_client_task
	adm_add_new_master_task_details
	get_adm_master_task_list 
	get_adm_task_list 
	get_pay_count
	get_current_week
	get_payroll_review_weeks
	get_payroll_week_of_interest
	get_current_pay_period
	update_standard_time
	update_standard_rate
	update_master_standard_time 
	get_pay_events
	check_for_weekend
	add_active_employees_to_payroll
	update_start_end_elapsed_for_payroll_hrs
	update_job_event_elapsed_adj
	update_job_headers_time_cost
	get_payroll_events
	update_daily_payroll_hours
	update_payroll_total_hours
	adjust_daily_payroll_hours
	update_weekly_payroll_employee_hours
	get_pay_week_dates
	get_payroll_weekly_list
	get_weekly_time_detail
	get_weekly_time_totals
	update_job_headers_with_time
	add_msg_payroll_hrs
	log_out_of_work
	log_break_time
	log_lunch_break
	get_now
	get_local_time
	get_today
	generate_task_event
	continue_hold_job  
	pause_current_job 
	close_current_job
	close_open_jobs
	get_new_count
	add_new_temp_employee
	update_start_job_open
	get_status
	get_open_job_count
	get_hold_client_count
	get_open_job
	get_client_count
  	add_new_client
	get_clients
	get_clients_all
	get_clients_not_on_hot_list
	get_client_name
	get_task_count
	get_daily_time_detail
	get_daily_time_summary
	update_daily_from_payroll_list
	update_daily_time_stats
	get_adm_task_count
	get_paused_job_count
	get_paused_jobs
	get_message
	get_session_message
	get_disabled_list 
	get_dimmed_list 
	get_paused_job_message_old
	log_job_event
	create_idle_event
	db_disconnect
	clear_payroll_temp
	write_payroll_temp_period
	update_payroll_temp_weeks
	get_agencies
	get_facilities
	clear_agency_hrs_temp
	write_agency_hrs_temp
	check_new_perm_status
	add_new_perm_employee
	check_employee_number
	get_employee_data
	update_employee
	create_tmp_tc_table
	create_tmp_task_table
	get_iteration_number
	adjust_tmp_task_table
	write_task_changes
	get_task_data
	get_task_job_list
	fill_tmp_tc_clean
	fill_tmp_tc_dirty
	update_clean_tc_tmp_table_PLACE_HOLDERS
	update_clean_tc_tmp_table
	fill_dirty_tc_tmp_table
	check_tc_tmp_data
	generate_clean_time_card_events
	generate_dirty_time_card_events
	count_job_events
	get_punch_in_out
	get_job_events
	get_time_card_start_end_sec
	get_time_card_data
	get_time_card_clients
	get_client_info
	change_client_info
	make_client_active
	create_job_headers_history
	create_job_events_history
	clear_job_events_in_tc
	get_rate
	create_new_job
	get_open_job_id
	get_open_job_client	
	get_hold_job_id
	get_hold_client_count
	get_hold_job_list
	update_day_event
	get_employee_list
	get_full_employee_list
	update_iteration
	deactivate_job_events
	write_new_dirty_job_events
	clear_day_events
	create_day_event
	clear_useless_iterations
	get_employee_rate
	get_match_count_temp
	backup_dbase
	fill_rpt_daily_data
	start_system_time
	end_system_time
	update_system_elapsed_time
	);
}

$author = "Alan Bak - Cine-Byte Digital";
$user_name = "traker";
$user_password = "mckenna";
$sql_server = "localhost";
$db_name = "ttraker";
$minTime = 4;
$back_loc = "/home/alan/Dropbox";

###################################################
# Server specific variables - Live System NO DNS BOXXY!
	$cgi_ref = "http:\/\/192.168.1.114\/cgi\/"; 	
	$js_ref = "http:\/\/192.168.1.114\/traker\/"; 	
	$css_ref = "http:\/\/192.168.1.114\/traker\/";
	$log = "\/opt\/lampp\/cgi-bin\/logs\/log.txt";
	$images = "\/opt\/lampp\/cgi-bin\/images\/";
#	$banner = "McKenna Logistics  2.2.77";
  	$banner= "<img src='http:\/\/192.168.1.114\/traker\/McKenna_RESIZED2.jpg' width='150' height='37'; alt='McKenna Logo'>";
###################################################
# Server specific variables - Development System
#	$cgi_ref = "http:\/\/ttdev\/cgi\/"; 	
#	$js_ref = "http:\/\/ttdev\/traker\/"; 	
#	$css_ref = "http:\/\/ttdev\/traker\/";
#	$log = "\/opt\/lampp\/cgi-bin\/logs\/log.txt";
#	$images = "\/opt\/lampp\/cgi-bin\/images\/";
#	$banner = "McKenna Logistics DEV 2.5.50";
###################################################
# Server specific variables - Live System
#	$cgi_ref = "http:\/\/timetracker\/cgi\/"; 	
#	$js_ref = "http:\/\/timetracker\/traker\/"; 	
#	$css_ref = "http:\/\/timetracker\/traker\/";
#	$log = "\/opt\/lampp\/cgi-bin\/logs\/log.txt";
#	$images = "\/opt\/lampp\/cgi-bin\/images\/";
#	$banner = "McKenna Logistics  2.2.60";
#  	$banner= "<img src='http:\/\/timetracker\/traker\/McKenna_RESIZED2.jpg' width='150' height='37'; alt='McKenna Logo'>";
###################################################
# Server specific variables - Live System NO DNS!
#	$cgi_ref = "http:\/\/192.0.0.58\/cgi\/"; 	
#	$js_ref = "http:\/\/192.0.0.58\/traker\/"; 	
#	$css_ref = "http:\/\/192.0.0.58\/traker\/";
#	$log = "\/opt\/lampp\/cgi-bin\/logs\/log.txt";
#	$images = "\/opt\/lampp\/cgi-bin\/images\/";
#	$banner = "McKenna Logistics  2.2.77";
#  	$banner= "<img src='http:\/\/192.0.0.58\/traker\/McKenna_RESIZED2.jpg' width='150' height='37'; alt='McKenna Logo'>";
###################################################
#  Server specific variables - Home VAL
#	$cgi_ref = "http:\/\/localhost\/cgi\/"; 	
#	$js_ref = "http:\/\/localhost\/traker\/"; 	
#	$css_ref = "http:\/\/localhost\/traker\/";
##	$css_ref = "http:\/\/localhost\/bright\/";
#	$log = "\/Applications\/XAMPP\/cgi-bin\/logs\/log.txt";
#	$images = "http:\/\/localhost\/traker\/";
#	$images = "\/Applications\/XAMPP\/cgi-bin\/images\/";
#  $banner= "<img src='http://localhost/traker//McKenna_RESIZED2.jpg' width='150' height='37'; alt='McKenna Logo'>";
#  $banner= "<img src='http://localhost/traker//McKenna_RESIZED2.jpg' width='239' height='60'; alt='McKenna Logo'>";

#	$banner = "McKenna Logistics   DEV 2.2.75";
#	$base_ref = "\'http:\/\/localhost\/dev_pm3\/\'";	
# 	$log = "\/Applications\/XAMPP\/htdocs\/dev_pm3\/logs\/log.txt";
#	$images = "\/Applications\/XAMPP\/htdocs\/images";
#	$banner = "3PL D-E-V Val august 24"; 
###################################################
# Server specific variables - Lap top
# 	$base_ref = "\'http:\/\/localhost\/dev_pm3\/\'";	
# 	$log = "\/Applications\/XAMPP\/htdocs\/dev_pm3\/log\/log.txt";
#	$images = "\/opt\/lampp\/htdocs\/dev_pm3\/images\/";
#	$banner = "3PL D-E-V Lap Top"; 
###################################################

sub initialize_dbi
{
   $drh = DBI->install_driver( 'mysql' );
   $dbh = DBI->connect("DBI:mysql:$db_name:$sql_server", $user_name, $user_password);
   die "Cannot connect: $D: $DBI::errstr\n"
      unless $dbh;
	my $sent_from = $ENV{'REMOTE_ADDR'};
#	my $now = get_now();
	print_log ("initializing db: $dbh");
}

sub print_log
{
	my $msg = $_[0];
	my $sent_from = $ENV{'REMOTE_ADDR'};
	my $now = get_now();
	open (LOG, ">> $log")
		or die "CAN NOT OPEN $log";	
	print LOG "$now,$sent_from,$msg\n";
	close LOG;    
}	

sub db_disconnect
{
	my $sent_from = $ENV{'REMOTE_ADDR'};
	my $now = get_now();
	open (LOG, ">> $log")
		or die "CAN NOT OPEN $log";	
	print LOG "$now,$sent_from,closing db,$dbh\n";
	close LOG;    
	$dbh->disconnect;   	 
}


sub run_statement
{
   my $stmt = "$_[0]";
#   print "$stmt <br/>";
   $sth = $dbh ->prepare($stmt);
   $sth->execute;
   open (LOG, ">> $log")
		or die "CAN NOT OPEN $log";	
	print LOG "$stmt\n";
	close LOG;
}

sub print_header 
{ 
	print "Content-type: text/html \n\n";
	print <<ENDHEADER;
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd"> 
ENDHEADER
} 

sub log_info
{
	my $string = $_[0];
	open (LOG, ">> $log")
		or die "CAN NOT OPEN $log";	
	print LOG "$string\n";
	close LOG;
}


sub log_in_screen_from_pm
{
my $msg = $_[0];
print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type="text/javascript" src="${js_ref}trak.js"></script>
<title>log_in_screen</title>
</head>
<h2><center>${banner}</center></h2>
<body onload="startTime();">
<hr/>
<div id="clock"></div>
<div id="box1">
&nbsp&nbsp&nbsp&nbsp&nbsp Employee ID: <br/><br/>
</div> 
<form id="box2" autocomplete="off" name="empID" action="${cgi_ref}trak_01_emp_log_in.pl" method="post">
   <input type="password" name=empID /><br/><br/><br/>
   <input type="submit" value="Log Into system">
   <br/><br/><br/>
   <b id="warn">$msg</b>
</form>
</body>
</html>
ENDHEADER
}

sub csr_activity_screen_from_pm
{
my $num = $_[0];
my $name = $_[1];
my $job_id = $_[2];
my $loc = $_[3];
my $warning = $_[4];
my $message = get_message ($num);

my $dis_ref = get_disabled_list ($num);
my @dis = @$dis_ref;

my $col_ref = get_dimmed_list ($num);
my @col = @$col_ref;

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>csr_activitty_screen</title>
</head>
<body onload='resizeWindow(500,500);startTime()'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
Activity:
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_02_csr_activity.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job_id'/>
<input type="hidden" name=empLoc value='$loc'/>

<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input type="radio" name="activity" value="1" checked="checked"/>&nbsp<$col[1]> Start tracking jobs</$col[1]><br/>
<input type="radio" name="activity" value="7"/><$col[7]>&nbsp Leave Work</$col[7]><br/>
<br/>
<input type='submit' value='Log Activity'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="warn">$warning</b><br/><br/>
<b id="info">$message</b><br/><br/>
</form>
</body>
</html>
ENDHEADER
}

sub csr_work_screen_from_pm
{
	my $num=$_[0];
	my $name=$_[1];
	my $job=$_[2];
	my $loc=$_[3];
	my $dir=$_[4];
	my $dirMsg;	
	my $stmnt = "select client_id, dir FROM job_open WHERE employee_num = $num";
	run_statement ($stmnt);
   	my ($CLI, $DIR) = $sth->fetchrow;
   	$sth->finish;
	if ($DIR eq 'IN') {
		$dirMsg = "INBOUND";
	} elsif ($DIR eq "OUT") {
		$dirMsg = "OUTBOUND";
	} else {
		$dirMsg = "OTHER";
	}	
	my $msg = "$dirMsg $CLI";
	my $inBut = my $outBut = my $othBut = "csr_button_normal";
	if ($dir eq "IN") {
		$inBut = "csr_button_hilight";
	} elsif ($dir eq "OUT") {
		$outBut = "csr_button_hilight";
	} elsif ($dir eq "OTH") {
		$othBut = "csr_button_hilight";
	} 
print <<ENDHEADER;
<html>
<head >
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>csr_work_screen</title>
</head >
<body onload='resizeHotlist();startTime_csr()'>
<div id='clock_csr'></div><br/>
<hr/>
<form id= 'box6_0' autocomplete='off' name='activity' action='${cgi_ref}trak_02_pay_activity.pl' method='post'>
<b><span id ='warn_small'>$name</span></b><br/><br/>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job'/>
<input type="hidden" name=empLoc value='$loc'/>
<input type="hidden" name=empDir value='$dir'/>
<button id='$inBut' type="BUTTON" name="direction" onClick="butEXE('${cgi_ref}trak_02_csr_hot_list.pl?num=$num&name=$name&job=$job&loc=$loc&dir=IN')">In</button>
<button id='$outBut' type="BUTTON" name="direction" onClick="butEXE('${cgi_ref}trak_02_csr_hot_list.pl?num=$num&name=$name&job=$job&loc=$loc&dir=OUT')">Out</button>
<button id='$othBut' type="BUTTON" name="direction" onClick="butEXE('${cgi_ref}trak_02_csr_hot_list.pl?num=$num&name=$name&job=$job&loc=$loc&dir=OTH')">Oth</button>
ENDHEADER
if ($dir eq "IN" || $dir eq "OUT" ) {
	print <<ENDHEADER;
	<div style = 'overflow:auto; height:500px; width:120px; border:.1'><br/>
	<table cellspacing='0' cellpadding='.1' border='.1' width='100px'><br/>
ENDHEADER
	my $hot_list_ref = get_hot_list($num);
	my @hot_list = @$hot_list_ref; 
	foreach $cli (@hot_list) {
		print <<ENDHEADER;
		<tr>
			<td id='csr_client_select'><a href='${cgi_ref}trak_02_csr_client_select.pl?num=$num&name=$name&job=$job&loc=$loc&dir=$dir&client=$cli'>$cli</a></td>
		</tr>
ENDHEADER
	}
	print <<ENDHEADER;
	</table><br/>
	</div>
	<button id='csr_button_wide' type="BUTTON" name="add_client" onClick="butEXE('${cgi_ref}trak_02_csr_add_hot_list_client.pl?num=$num&name=$name&job=$job&loc=$loc&dir=IN')">Add Client</button><br/>

	<button id='csr_button_wide' type="BUTTON" name="drop_client" onClick="butEXE('${cgi_ref}trak_02_csr_drop_hot_list_client.pl?num=$num&name=$name&job=$job&loc=$loc&dir=IN')">Drop Client</button>

ENDHEADER
	} elsif ($dir eq "OTH") {
		print <<ENDHEADER;
		<br/>
		<table cellspacing='0' cellpadding='.1' border='.1' width='100px'><br/>
ENDHEADER

		my $hot_list_ref = get_hot_list_other($num);
		my @hot_list = @$hot_list_ref; 
		foreach $cli (@hot_list) {
		print <<ENDHEADER;
		<tr>
			<td id='csr_client_select'><a href='${cgi_ref}trak_02_csr_client_select.pl?num=$num&name=$name&job=$job&loc=$loc&dir=$dir&client=$cli'>$cli</a></td>
		</tr>
ENDHEADER
		} 
	print "</table>";
	}
	print <<ENDHEADER;
	<br/><br/>
	<a id="small" href="${cgi_ref}trak_00_csr_activity.pl?num=$num&name=$name&job=$job&loc=$loc"> Return</a>
ENDHEADER
	if ( $dir eq "NONE" ) {
		print "<br/><br/>";
		print "<b id='info_small'>$msg</b><br/>"	
	}
	print <<ENDHEADER;
	</form>	
	</body>
	</html>
ENDHEADER
}

sub get_hot_list
{
	my $num = $_[0];
  	my @hot_list = ();
  	my $tmp;
  	my $stmnt = "select client_id FROM csr_hot_list WHERE employee_num = $num";
  	run_statement ($stmnt);
  	while (($tmp) = $sth->fetchrow) {
	    push @hot_list, $tmp;
  	}
	$sth->finish;
	my @hot_list = sort(@hot_list);
	return \@hot_list;	
}

sub get_hot_list_other
{
	my $num = $_[0];
  	my @hot_list = ();
  	my $tmp;
	my $stmnt = "SELECT client_id FROM clients WHERE csr_grp < -9 ORDER by csr_grp DESC";
  	run_statement ($stmnt);
  	while (($tmp) = $sth->fetchrow) {
	    push @hot_list, $tmp;
  	}
	$sth->finish;
	return \@hot_list;	
}

sub add_to_hot_list
{
	my $num=$_[0];
	my $cli=$_[1];
	my $stmnt = "SELECT COUNT(*) FROM csr_hot_list WHERE client_id = '$cli' AND employee_num = $num";
	run_statement ($stmnt);

	if ( $count == 0 ) {
		$stmnt = "INSERT INTO csr_hot_list (employee_num, client_id ) VALUES ($num, '$cli')";
		run_statement ($stmnt);
	}
	$sth->finish;
}

sub drop_from_hot_list
{
	my $num=$_[0];
	my $cli=$_[1];
	my $stmnt = "DELETE FROM csr_hot_list WHERE employee_num = $num AND client_id = '$cli'";
	run_statement ($stmnt);
	$sth->finish;
}


sub clear_hold_jobs 
{
	my $num = $_[0];
	my $stmnt = "DELETE FROM job_hold WHERE employee_num = $num";
  	run_statement ($stmnt);
	$sth->finish;
}

sub pay_activity_screen_from_pm
{
my $num=$_[0];
my $name=$_[1];
my $job=$_[2];
my $loc=$_[3];
my $msg=$_[4];

my @dis = ("", "", "", "", "", "", "", "","disabled=\"disabled\"");
my @col = ("dis","en","en","en","en","en","en","en","en","en","dis"); 

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>payroll_activity_screen</title>
</head>
<body onload='startTime();'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
Activity:
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_02_pay_activity.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job'/>
<input type="hidden" name=empLoc value='$loc'/>
<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input $dis[1] type="radio" name="activity" value="1" checked="checked"/>&nbsp<$col[1]> Generate Payroll Table</$col[1]><br/>
<input $dis[2] type="radio" name="activity" value="2"/>&nbsp<$col[2]> Generate Agency Hours Table<br/>
<input $dis[3] type="radio" name="activity" value="3"/>&nbsp<$col[3]> Add New Permanent Employee </$col[3]><br/>
<input $dis[4] type="radio" name="activity" value="4"/>&nbsp<$col[4]> Add New Temp Employee</$col[4]><br/>
<input $dis[5] type="radio" name="activity" value="5"/>&nbsp<$col[5]> Modify Employee Data</$col[5]><br/>
<input $dis[6] type="radio" name="activity" value="6"/>&nbsp<$col[6]> Close Payroll Dates</$col[5]><br/>
<input $dis[7] type="radio" name="activity" value="7"/>&nbsp<$col[6]> Open Payroll Dates</$col[5]><br/>
ENDHEADER

# <input $dis[8] disabled="disabled" type="radio" name="activity" value="8"/>&nbsp<$col[6]> TBA</$col[5]><br/>
print <<ENDHEADER;
<br/>
<input type='submit' value='Proceed'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="info">$msg</b>
<br/><br/>
</form>
</body>
</html>

ENDHEADER
}

sub adm_activity_screen_from_pm
{
	my $num = $_[0];
	my $name = $_[1];
	my $job = $_[2];
	my $loc = $_[3];
	my $msg = $_[4];

my @dis = ("", "", "", "", "", "", "", "","disabled=\"disabled\"");
my @col = ("dis","en","en","en","en","en","en","en"); 

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>adm_activity_screen</title>
</head>
<body onload='startTime();'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
Activity:
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_02_adm_activity.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job'/>
<input type="hidden" name=empLoc value='$loc'/>
<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input type="radio" name="activity" value="1" checked="checked"/>&nbsp<$col[1]> Adjust Client Standard Time/Rate</$col[1]><br/>
<input $dis[2] type="radio" name="activity" value="2"/>&nbsp<$col[2]> Adjust Master Standard Time</$col[2]><br/>
<input $dis[3] type="radio" name="activity" value="3"/>&nbsp<$col[3]> Add New Client</$col[3]><br/>
<input $dis[4] type="radio" name="activity" value="4"/>&nbsp<$col[4]> Add Client Task</$col[4]><br/>
<input $dis[5] type="radio" name="activity" value="5"/>&nbsp<$col[5]> Add Master Task</$col[5]><br/>
<input $dis[6] type="radio" name="activity" value="6"/>&nbsp<$col[6]> Update Client Information</$col[6]><br/>
<input $dis[6] type="radio" name="activity" value="7"/>&nbsp<$col[7]> Reports<br/>
<br/>
<input type='submit' value='Proceed'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="info">$msg</b>
<br/><br/>
</form>
</body>
</html>

ENDHEADER
}


sub sup_activity_screen_from_pm
{
my $num = $_[0];
my $name = $_[1];
my $job = $_[2];
my $loc = $_[3];
my $msg = $_[4];

my $week_ref = get_payroll_review_weeks();
my @weeks = @$week_ref;

my @dis = ("", "", "", "", "", "", "", "","","disabled=\"disabled\"");
my @col = ("dis","en","en","en","en","en","en","en", "en", "dis"); 

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>sup_activity_screen</title>
</head>
<body onload='startTime();'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
Activity:
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_02_sup_activity.pl' method='post'>
<input type="hidden" name=supNum value='$num'/>
<input type="hidden" name=supName value='$name'/>
<input type="hidden" name=supJob value='$job'/>
<input type="hidden" name=supLoc value='$loc'/>
<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input $dis[1]type="radio" name="activity" value="1" checked="checked"/>&nbsp<$col[1]> Confirm Daily Payroll</$col[1]><br/>
<input $dis[2]type="radio" name="activity" value="$weeks[0][0]"/>&nbsp<$col[2]> Review Payroll - $weeks[0][1] </$col[2]><br/>
<input $dis[3] type="radio" name="activity" value="$weeks[1][0]"/>&nbsp<$col[3]> Review Payroll - $weeks[1][1] </$col[3]><br/>
<input $dis[4] type="radio" name="activity" value="$weeks[2][0]"/>&nbsp<$col[4]> Review Payroll - $weeks[2][1]</$col[4]><br/>
<input $dis[5] type="radio" name="activity" value="5"/>&nbsp<$col[5]> Add New Temp Employee</$col[5]><br/>
<input $dis[6] type="radio" name="activity" value="6"/>&nbsp<$col[6]> Who is logged in?</$col[6]><br/>
<input $dis[7] type="radio" name="activity" value="7"/>&nbsp<$col[7]> Enter Data from Time Cards</$col[6]><br/>
<input $dis[8] type="radio" name="activity" value="8"/>&nbsp<$col[8]> Modify Tasks for Individual Jobs</$col[7]><br/>
ENDHEADER
# <input $dis[9] disabled="disabled" type="radio" name="activity" value="9"/>&nbsp<$col[9]> TBA</$col[9]><br/>
print <<ENDHEADER;
<br/>
<input type='submit' value='Proceed'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="info">$msg</b>
<br/><br/>
</form>
</body>
</html>

ENDHEADER
}

sub activity_screen_from_pm
{
my $num = $_[0];
my $name = $_[1];
my $job_id = $_[2];
my $loc = $_[3];
my $warning = $_[4];
my $message = get_message ($num);

my $dis_ref = get_disabled_list ($num);
my @dis = @$dis_ref;

my $col_ref = get_dimmed_list ($num);
my @col = @$col_ref;

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>activity_screen</title>
</head>
<body onload='startTime();'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
Activity:
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_02_activity.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job_id'/>
<input type="hidden" name=empLoc value='$loc'/>

<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input type="radio" name="activity" value="1" checked="checked"/>&nbsp<$col[1]> Start Job</$col[1]><br/>
<input $dis[2] type="radio" name="activity" value="2"/>&nbsp<$col[2]> Close Job (do not log tasks)</$col[2]><br/>
<input $dis[3] type="radio" name="activity" value="3"/>&nbsp<$col[3]> Log Tasks</$col[3]><br/>
<input $dis[8] type="radio" name="activity" value="8"/>&nbsp<$col[8]> Log Batch Tasks</$col[8]><br/>
<input $dis[4] type="radio" name="activity" value="4"/>&nbsp<$col[4]> Continue Job</$col[4]><br/>
<input $dis[5] type="radio" name="activity" value="5"/>&nbsp<$col[5]> Lunch</$col[5]><br/>
<input $dis[6] type="radio" name="activity" value="6"/>&nbsp<$col[6]> Break</$col[6]><br/>
<input type="radio" name="activity" value="7"/><$col[7]>&nbsp Leave Work</$col[7]><br/>
<br/>
<input type='submit' value='Log Activity'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="warn">$warning</b><br/><br/>
<b id="info">$message</b><br/><br/>
</form>
</body>
</html>
ENDHEADER
}

sub batch_job_select_from_pm 
{
	my $num = $_[0];
	my $name = $_[1];
	my $job_id = $_[2];
	my $loc = $_[3];
	my $batchNum = $_[4];
	my $holdList_ref = $_[5];
	my $activity =  $_[6];
	my $cur_job = $_[7];
	my @holdList = @$holdList_ref;
	my $open_job_ref = get_open_job ($num);
	my @open_job = @$open_job_ref;
	my $curDoc = $open_job[0];
	my $curCli = $open_job[3];

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>batch_job_select</title>
</head>
<body onload='startTime()'>
<h2><center>${banner}</center></h2>
<hr/>
<h3>Add Batch Jobs to Current Job : $curCli &nbsp&nbsp $curDoc</h3>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
<br/>
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_03_batch_tasks.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job_id'/>
<input type="hidden" name=empLoc value='$loc'/>
<input type="hidden" name=batNum value='$batchNum'/>
<input type="hidden" name=empAct value='$activity'/>

<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<table cellspacing="0" cellpadding="3" border="0" width="70%">
	<tr>
     	<th align=center width="10%"></th>
 		<th align=left width="25%">Client</th>
		<th align=left width="25%">Doc #</th>
	</tr>
ENDHEADER
for my $i (0..$#holdList) {
print <<ENDHEADER;
	<tr>
		<td align=center ><input type='checkbox' name='HOLDID_$holdList[$i][0]' /></td>
		<td align=left >$holdList[$i][2]</td>
		<td align=left>$holdList[$i][1]</td>
	</tr>
ENDHEADER
		
}
print <<ENDHEADER;
</table>
<br/>
<input type='submit' value='Batch Jobs'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="warn">$warning</b><br/><br/>
<b id="info">$message</b><br/><br/>
</form>
</body>
</html>
ENDHEADER
}	

sub add_hold_to_batch
{
	my $job = $_[0];
	my $bat =  $_[1];
	my $stmnt = "UPDATE job_hold SET batch = $bat WHERE job_num = $job";
	run_statement ($stmnt);
	$stmnt = "UPDATE job_headers SET batch = $bat WHERE job_num = $job";
	run_statement ($stmnt);
	$sth->finish;
}

sub clear_batch_jobs_from_hold
{
	my $batch =  $_[0];
	my $stmnt = "DELETE FROM job_hold WHERE batch = $batch";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$sth->finish;
}

sub open_report_screen
{
my $num=$_[0];
my $name=$_[1];
my $job=$_[2];
my $loc=$_[3];
my @dis = ("", "", "disabled=\"disabled\"", "disabled=\"disabled\"", "disabled=\"disabled\"", "disabled=\"disabled\"", "disabled=\"disabled\"", "disabled=\"disabled\"","disabled=\"disabled\"");
my @col = ("dis","en","dis","dis","dis","dis","dis","dis","dis","dis","dis"); 

print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>open_reports</title>
</head>
<body onload='startTime()'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number: <br/><br/>
Employee Name: <br/><br/>
Activity:
</div>
<form id='box2' autocomplete='off' name='activity' action='${cgi_ref}trak_02_rpt_generator.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job'/>
<input type="hidden" name=empLoc value='$loc'/>
<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input $dis[1] type="radio" name="activity" value="1" checked="checked"/>&nbsp<$col[1]> Daily Reports</$col[1]><br/>
ENDHEADER
# <input $dis[2] type="radio" name="activity" value="2"/>&nbsp<$col[2]> TBA</$col[2]><br/>
# <input $dis[3] type="radio" name="activity" value="3"/>&nbsp<$col[3]> TBA </$col[3]><br/>
# <input $dis[4] type="radio" name="activity" value="4"/>&nbsp<$col[4]> TBA</$col[4]><br/>
# <input $dis[5] type="radio" name="activity" value="5"/>&nbsp<$col[5]> TBA</$col[5]><br/>
# <input $dis[6] type="radio" name="activity" value="6"/>&nbsp<$col[6]> TBA</$col[5]><br/>
# <input $dis[7] type="radio" name="activity" value="7"/>&nbsp<$col[6]> TBA</$col[5]><br/>
print <<ENDHEADER;

<br/>
<input type='submit' value='Proceed'>
<br/><br/>
<a href="${cgi_ref}trak_00_adm_activity.pl?num=$num&name=$name&job=$job&loc=$loc">Return to Activities</a>
<br/><br/>

</form>
</body>
</html>

ENDHEADER

}

sub start_job_screen_from_pm
{
my $num = $_[0];
my $name = $_[1];
my $job_id = $_[2];
my $loc = $_[3];
my $activity = $_[4];
my $session = $_[5];
my $msg = $_[6];

my $client_ref = get_clients(); 
my @clients = @$client_ref;
print <<ENDHEADER;
<html>
<head>
<link rel='stylesheet' type='text/css' href='${css_ref}trak.css' />
<script type='text/javascript' src='${js_ref}trak.js'></script>
<title>start_job</title>
</head>
<body onload='startUp1()'>
<h2><center>${banner}</center></h2>
<hr/>
<div id='clock'></div>
<div id='box1'>
Employee Number:<br/><br/>
Employee Name:<br/><br/>
Document Number:<br/><br/>
Client ID:<br/><br/>
</div>
<form id='box2' autocomplete='off' name='startJob' action='${cgi_ref}trak_03_initiate_job.pl' method='post'>
<input type="hidden" name=empNum value='$num'/>
<input type="hidden" name=empName value='$name'/>
<input type="hidden" name=empJob value='$job_id'/>
<input type="hidden" name=empLoc value='$loc'/>
<input type="hidden" name=empAct value='$activity'/>
<input type="hidden" name=empSes value='$session'/>

<span>$num</span><br/><br/>
<span>$name</span></b><br/><br/>
<input type="text" name="docNum" size=10 tabindex=1 /><br/><br/>
<select id=clientDrop name="cliId" tabindex=2 >

ENDHEADER

# add a blank element at the start of the list
print "<option value='NUL'></option>";
# add all clients on the list to the drop down
foreach my $cli (@clients) {
   print "<option value=$cli >$cli</option>";
}

print <<ENDHEADER;

</select>
<br/><br/>
<input type='submit' name='submit' value='Start New Job'>
<br/><br/>
<a href="${cgi_ref}trak_00_start_up.pl?num=$num">Return to Log-In</a>
<br/><br/>
<b id="info">$msg</b>
<br/><br/>
</form>
</body>
</html>

ENDHEADER
}

sub open_report_daily_screen
{


}

sub close_window 
{
	print "Content-type: text/html \n\n";  
	print "self.close()";
	exit 0;
}

sub parse_input
{
	my $sent_from = $ENV{'REMOTE_ADDR'};
	my %FORM_DATA;
	my @key_value_pairs =();
	my $pair;
	my $count = keys %FORM_DATA;
	my $c = 1;
	$whichmethod = $ENV{'REQUEST_METHOD'};
	if($whichmethod eq "GET"){ 
		$forminfo = $ENV{"QUERY_STRING"};
	}elsif ($whichmethod eq "POST") {
		$forminfo = <STDIN>; 
	} else {
		print "NO VALID METHOD FOUND $whichmethod?<br/>";
	}
	@key_value_pairs = split(/&/,$forminfo); 
	foreach $pair (@key_value_pairs){
		($key,$value) = split(/=/,$pair);
		$value =~ s/\+/ /g; 
		$value =~ s/%([0-9a-fA-F][0-9a-fA-F])/pack("C", hex($1))/eg;
		$value =~ s/'/\\'/g;
		$FORM_DATA{$key} = $value;
	}
   return \%FORM_DATA;
}      

sub get_employee_count
{
	my $var = $_[0];
#	print "OLA GOT $var<br/>";
	my $stmnt;
	if ( $var =~ m/^\d+$/ ) {
#		print "using NUMBER<br>";
		$stmnt = "select COUNT(*) from employees where employee_num = '$var'";
	} else {	 
#		print "using LOGIN<br>";
		$stmnt = "select COUNT(*) from employees where log_id = '$var'";
	}
#	print "$stmnt<br/>";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $head_count = $rs->[0]->[0];
	$sth->finish;
#	print "COUNT IS $head_count<br/>";
	return $head_count;
}

sub get_employee_info
{
	my $var = $_[0];
#	print "OLA GOT $var<br/>";
	my $stmnt;
	if ( $var =~ m/^\d+$/ ) {
#		print "using NUMBER<br>";
		$stmnt = "select employee_num, first_name, last_name, job, location, facility from employees where employee_num = '$var'";
	} else {	 
#		print "using LOGIN<br>";
		$stmnt = "select employee_num, first_name, last_name, job, location, facility from employees where log_id = '$var'";
	}	
   & run_statement ($stmnt);
   my @info = $sth->fetchrow;
   $sth->finish;
   return \@info;
}

sub get_log_in_count
{
	my $num = "$_[0]"; 
	my $stmnt = "select COUNT(*) from logged_in where employee_num = $num";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $count = $rs->[0]->[0];
	$sth->finish;
	return $count;  
}

sub log_in_get_status
{
	my $num = "$_[0]"; 
	my $now = "$_[1]";
	my @tmp = ();
	my $stmnt = "select TIME_TO_SEC(TIMEDIFF('$now', start))/60, start, TIMEDIFF('$now',start), DAYNAME(DATE(start)), MONTHNAME(DATE(start)), DAY(DATE(start)), YEAR(DATE(start)), TIME(start), DATEDIFF('$now', start), DATE(start) FROM logged_in WHERE employee_num = $num";
	& run_statement ($stmnt);
   my ($elapsed_log_in, $start, $log_hrs, $dow, $month, $day, $year, $time, $nod, $date) = $sth->fetchrow;
   $sth->finish;
	$stmnt = "select TIME_TO_SEC(TIMEDIFF('$now', stamp))/60, stamp, TIMEDIFF('$now', stamp) FROM job_open WHERE employee_num = $num";
#	print "$stmnt<br/>";
	& run_statement ($stmnt);
   my ($elapsed_job, $stamp, $job_hrs) = $sth->fetchrow;
	$sth->finish;

   open (LOG, ">> $log")
		or die "CAN NOT OPEN $log";	
	print LOG "LOG IN STATUS DATA:\n";
	print LOG "elapsed_log_in, elapsed_job, start, stamp, log_hrs, job_hrs, dow, month, day, year, time, nod, date\n";
	print LOG "$elapsed_log_in, $elapsed_job, $start, $stamp, $log_hrs, $job_hrs, $dow, $month, $day, $year, $time, $nod, $date\n";
	close LOG;    

	@tmp = ($elapsed_log_in, $elapsed_job, $start, $stamp, $log_hrs, $job_hrs, $dow, $month, $day, $year, $time, $nod, $date);
	return \@tmp;
}

sub time_stamp_compare
{
	my $start  = "$_[0]";
	my $new_now = "$_[1]";
	my $stmnt = "SELECT TIME_TO_SEC(TIMEDIFF('$new_now', '$start'))";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $diff = $rs->[0]->[0];
	$sth->finish;
	return $diff;  
}

sub get_payroll_count
{
	my $num = "$_[0]";
	my $today = "$_[1]";
	my $stmnt = "SELECT COUNT(*) FROM payroll_hrs WHERE employee_num = $num AND date = '$today'";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $pay_count = $rs->[0]->[0];
	$sth->finish;
	return $pay_count
}

sub get_payroll_days
{
  my @payDays = ();
  my $tmp;
  my $stmnt = "SELECT date, COUNT(*) FROM payroll_hrs WHERE hours IS NULL GROUP BY date ORDER BY date";
  & run_statement ($stmnt);
  while (($tmp) = $sth->fetchrow) {
    push @payDays, $tmp;
  }
  $sth->finish;
  return \@payDays;
}

sub get_payroll_dates
{
  my @payDates = ();
  my $tmp;
  my $stmnt = "select date FROM payroll_dates WHERE open = 'Y' AND date < CURDATE() ORDER BY date DESC";
  & run_statement ($stmnt);
  while (($tmp) = $sth->fetchrow) {
    push @payDates, $tmp;
  }
  $sth->finish;
  return \@payDates;
}

sub get_closed_payroll_dates
{
  my @nonPayDates = ();
  my $tmp;
  my $stmnt = "select date FROM payroll_dates WHERE open = 'N' AND date < CURDATE() ORDER BY date DESC";
  & run_statement ($stmnt);
  while (($tmp) = $sth->fetchrow) {
    push @nonPayDates, $tmp;
  }
  $sth->finish;
  return \@nonPayDates;
}

sub get_payroll_dates_incl_today
{
  my @payDates = ();
  my $tmp;
  my $stmnt = " select date FROM payroll_dates WHERE open = 'Y' AND date <= CURDATE() ORDER BY date DESC";
  & run_statement ($stmnt);
  while (($tmp) = $sth->fetchrow) {
    push @payDates, $tmp;
  }
  $sth->finish;
  return \@payDates;
}

sub	close_payroll_dates
{
	my $date = $_[0];
	my $stmnt = "UPDATE payroll_dates SET open = 'N' WHERE date <= '$date' AND open = 'Y'";
	& run_statement ($stmnt);
	$sth->finish;
}

sub open_payroll_dates
{
	my $date = $_[0];
	my $stmnt = "UPDATE payroll_dates SET open = 'Y' WHERE date >= '$date' AND open = 'N'";	
	& run_statement ($stmnt);
	$sth->finish;
}

sub get_payroll_periods
{
  my @payPeriods = ();
  my @tmp;
  my $stmnt = "SELECT pay_num, start, end  FROM payroll_periods WHERE start < NOW() AND active = 'Y' ORDER BY pay_num DESC;";
  & run_statement ($stmnt);
   while (@tmp = $sth->fetchrow) {
    	push @payPeriods, [@tmp];
  }
  $sth->finish;
  return \@payPeriods;
}

sub create_payroll_event
{
	my $num = "$_[0]";
	my $date = "$_[1]";
	my $rate = get_employee_rate ($num);
	my $stmnt = "INSERT INTO payroll_hrs (date, employee_num, rate) VALUES ('$date', $num, $rate)";
	& run_statement ($stmnt);
	$sth->finish;
	$stmnt = " select week_num, pay_num FROM payroll_dates WHERE date = '$date'";
   & run_statement ($stmnt);
   my ($week, $pay) = $sth->fetchrow;
	$sth->finish;
	$stmnt = "UPDATE payroll_hrs SET week_num = '$week', pay_num = '$pay' WHERE employee_num = $num and date = '$date'";
	& run_statement ($stmnt);
	$sth->finish;
}

sub get_current_work_list
{
	my $loc= "$_[0]";
	my @tmp = ();
	my @log_ins = ();
	my $stmnt = "SELECT L.employee_num, CONCAT (E.first_name, ' ' , E.last_name), L.start, J.doc_num, J.client_id, TIME(J.stamp) FROM logged_in AS L JOIN job_open AS J ON (J.employee_num = L.employee_num) JOIN employees AS E on (E.employee_num = L.employee_num) WHERE E.location = '$loc' ORDER BY DATE(L.start), E.employee_num";
#	my $stmnt = "SELECT E.employee_num, concat(E.first_name, ' ' , E.last_name) AS name, J.doc_num, J.client_id, J.stamp from employees AS E JOIN job_open AS J ON (E.employee_num = J.employee_num) WHERE E.location = '$loc' ORDER BY E.employee_num";
	& run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @log_ins, [@tmp];
	}
	$sth->finish;
	return \@log_ins;
}

sub log_employee_in
{
	my $num = "$_[0]";
	my $now = "$_[1]";   
	my $stmnt = "INSERT INTO day_events (employee_num, start) VALUES ($num, '$now')";
	& run_statement ($stmnt);
	$sth->finish;
	$stmnt = "INSERT INTO logged_in (day_event_num, employee_num, start) VALUES (LAST_INSERT_ID(),$num, '$now')";
	& run_statement ($stmnt);
	$sth->finish;
}

sub calc_elapsed_day_time
{ 
   my $day_num = "$_[0]"; 
   my $now = "$_[1]";
   my $stmnt = "UPDATE day_events SET end = '$now', elapsed = TIMEDIFF('$now', start) WHERE day_event_num = $day_num";
   & run_statement ($stmnt);
   $sth->finish;
   return;   
}

sub calc_elapsed_minutes
{ 
   my $start = "$_[0]"; 
   my $end= "$_[1]";
   my $stmnt = "SELECT TIME_TO_SEC(TIMEDIFF('$end', '$start'))/60";
   $sth = $dbh->prepare($stmnt); 
   $sth->execute;
   my $elapsed = $sth->fetchrow;
   $sth->finish;
   return $elapsed;  
}

sub calc_elapsed_seconds
{ 
   $start = "$_[0]"; 
   $end= "$_[1]";
   $sth = $dbh->prepare("SELECT TIME_TO_SEC(TIMEDIFF('$end', '$start'))"); 
   $sth->execute;
   $elapsed_sec = $sth->fetchrow;
   $sth->finish;
   return $elapsed_sec;  
}

sub update_view_temp_tasks_NOT_USED
{
   my $client = $_[0];
   print "TRYING TO MAKE A CLIENT: $client<br/>";
   $stmnt = "DROP VIEW IF EXISTS temp_tasks";   
   & run_statement ($stmnt);
   $sth->finish;
   print "VIEW SHOULD BE DROPPED<br/>";
   $stmnt = "CREATE VIEW temp_tasks AS SELECT * FROM tasks WHERE client_id = '$client'";   
   & run_statement ($stmnt);
   $sth->finish;
}

sub generate_task_event  
{
	my $job = "$_[0]";
	my $task = "$_[1]";
	my $code = "$_[2]";
	my $vol = "$_[3]";
	my $now = "$_[4]";
	my $stmnt = "INSERT INTO task_events (job_num, task_num, task_code, volume, stamp) VALUES ($job, $task, '$code', $vol, '$now')";
#	open (OUT, ">> $log");
#	print OUT "$job,$task,$code,$vol,$stmnt\n";
#	close (OUT);
	# print "$stmnt<br/>";
	& run_statement ($stmnt);
	$sth->finish;
}

sub continue_hold_job
{
	
   my $id = "$_[0]";
   my $now = "$_[1]";
#    print "OYYYY $id, $now CONTINUE!!!)<br/>";
   my $stmnt = "SELECT  session, doc_num, job_num, employee_num, client_id FROM job_hold WHERE id = $id";
#   print "$stmnt<br/>";
   run_statement ($stmnt);
   my ($session, $doc, $job_num, $emp, $cli) = $sth->fetchrow;
   $sth->finish;
   if ( $cli eq "" ) {
   	$stmnt = "INSERT INTO job_open (doc_num , job_num, session, employee_num, client_id, stamp) VALUES ($doc, $job_num, $session, $task, $emp, NULL, '$now')";
   } elsif ( $doc eq "" ) {
		$stmnt = "INSERT INTO job_open (doc_num , job_num, session, employee_num, client_id, stamp) VALUES (NULL, $job_num, $session, $emp, '$cli', '$now')";
   } else {
		$stmnt = "INSERT INTO job_open (doc_num , job_num, session, employee_num, client_id, stamp) VALUES ($doc, $job_num, $session, $emp, '$cli', '$now')";
  }
#  print "$stmnt<br/>";
  	run_statement ($stmnt);
  	$sth->finish;
	$stmnt = "DELETE FROM job_hold  WHERE id = $id";
	run_statement ($stmnt);
	$sth->finish;
}

sub update_task_std_time
{
   my $job = $_[0];
   my $stmnt = "UPDATE task_events AS TE JOIN tasks AS T ON (TE.task_code = T.chrg_code)
				JOIN job_headers AS JH ON (JH.client_id = T.client_id AND JH.job_num = TE.job_num)
				SET TE.std_time = T.std_min*TE.volume, TE.revenue = T.rate*TE.volume 
				WHERE T.std_min IS NOT NULL AND TE.job_num = $job";
   & run_statement ($stmnt);
   $sth->finish;
#   print "$stmnt<br/>";
}

sub get_task_list
{
	my $client = $_[0];
	my $job_id = $_[1];
	my $fld_str = $_[2];
	my @tasks=();
	my @tmp=();
	my $stmnt;
	if ( $job_id eq "WHS" || $job_id eq "TMP") {
   		$stmnt = "SELECT chrg_code, description, units FROM tasks WHERE client_id = '$client' AND show_whs = 'Y' AND $fld_str = 'Y' ORDER BY whs_priority";
	} elsif ( $job_id eq "CSR" ) {
   		$stmnt = "SELECT chrg_code, description, units FROM tasks WHERE client_id = '$client' AND show_csr = 'Y' AND $fld_str = 'Y' ORDER BY csr_priority";
	} else {
   		$stmnt = "SELECT chrg_code, description, units FROM tasks WHERE client_id = '$client' AND show_adm = 'Y' AND $fld_str = 'Y' ORDER BY adm_priority";
	}   
	run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
	   	push @tasks, [@tmp];
	}
	$sth->finish;
	return \@tasks;
}

sub adm_new_cli_task_verify
{
	$cli = "$_[0]";
	$task  = "$_[1]";
	$stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli' AND chrg_code = '$task'";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$count = $rs->[0]->[0];
	$sth->finish;
	return $count;  
}

sub adm_new_master_task_verify
{
	$task  = "$_[0]";
	$stmnt = "SELECT COUNT(*) FROM master_tasks WHERE chrg_code = '$task'";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$count = $rs->[0]->[0];
	$sth->finish;
	return $count;  
}

sub adm_add_new_client_task
{
	$cli = "$_[0]";
	$task = "$_[1]";
	$desc = "$_[2]";
	$units = "$_[3]";
	$stdMin = "$_[4]";
	$stdRate = "$_[5]";
	$whs = "$_[6]";
	if ($whs eq "on" ) { $Whs="Y"; } else {$Whs="N";}	
	$csr = "$_[7]";
	if ($csr eq "on" ) { $Csr="Y"; } else {$Csr="N";}	
	$admin = "$_[8]";
	if ($admin eq "on" ) { $Admin="Y"; } else {$Admin="N";}	
	$in = "$_[9]";
	if ($in eq "on" ) { $In="Y"; } else {$In="N";}	
	$out = "$_[10]";
	if ($out eq "on" ) { $Out="Y"; } else {$Out="N";}	
	$stmnt = "INSERT INTO tasks (client_id, chrg_code, description, units, show_whs, show_csr, show_adm, inbound, outbound, other, include, 
			whs_priority, csr_priority, adm_priority, maves_chrg_code, rev_code, std_min, rate) 
			VALUES ('$cli', '$task', '$desc', '$units', '$Whs', '$Csr', '$Admin', '$In', '$Out', 'Y', 'Y', 100, 100, 100, NULL, NULL, $stdMin, $stdRate)";
   	& run_statement ($stmnt);
	$sth->finish;
#	print "$stmnt";
}

sub	adm_add_new_master_task_details
{
	$task = "$_[0]";
	$desc = "$_[1]";
	$units = "$_[2]";
	$stdMin = "$_[3]";
	$whs = "$_[4]";
	if ($whs eq "on" ) { $Whs="Y"; } else {$Whs="N";}	
	$csr = "$_[5]";
	if ($csr eq "on" ) { $Csr="Y"; } else {$Csr="N";}	
	$admin = "$_[6]";
	if ($admin eq "on" ) { $Admin="Y"; } else {$Admin="N";}	
	$in = "$_[7]";
	if ($in eq "on" ) { $In="Y"; } else {$In="N";}	
	$out = "$_[8]";
	if ($out eq "on" ) { $Out="Y"; } else {$Out="N";}	
	$stmnt = "INSERT INTO master_tasks (client_id, chrg_code, description,  units, show_whs, show_csr, show_adm, inbound, outbound, other, include, whs_priority, csr_priority, adm_priority, maves_chrg_code, rev_code, std_min) VALUES ('MASTER', '$task', '$desc', '$units', '$Whs', '$Csr', '$Admin', '$In', '$Out', 'Y', 'Y', 100, 100, 100, NULL, NULL, $stdMin)";
   	& run_statement ($stmnt);
	$sth->finish;
}

sub get_adm_master_task_list
{
   my @master_tasks = ();
   $stmnt = "SELECT client_id, chrg_code, description, units, std_min FROM master_tasks ORDER BY chrg_code";
   run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) {
      push @master_tasks, [@tmp];
   }
   $sth->finish;
   return \@master_tasks;
}

sub get_adm_task_list
{
   my $cli = $_[0];
   my @tasks = ();
   $stmnt = "SELECT client_id, chrg_code, description, units, std_min, rate FROM tasks WHERE client_id = '$cli' ORDER BY chrg_code";
   & run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) {
      push @tasks, [@tmp];
   }
   $sth->finish;
   return \@tasks;
}

sub get_pay_count
{
   my $loc = $_[0];
   my $payDate = $_[1];
   $sth = $dbh->prepare("SELECT COUNT(*) FROM payroll_hrs AS PH JOIN employees AS E ON (E.employee_num = PH.employee_num) WHERE PH.date = '$payDate' AND E.location = '$loc'");
   $sth->execute;
   $pay_count = $sth->fetchrow;
   $sth->finish;
#   print "SELECT COUNT(*) FROM payroll_hrs WHERE hours IS NULL AND date = '$payDate' - RETURNS $pay_count <br/>";
   return $pay_count; 
}

sub get_current_week
{
	$stmnt = "SELECT week_num FROM payroll_dates WHERE date = CURDATE()"; 
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $week = $rs->[0]->[0];
	$sth->finish;
	return $week;
}

sub get_payroll_review_weeks
{
	my @weeks = ();
	$stmnt = "SELECT week_num FROM payroll_dates WHERE date = CURDATE()"; 
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $wk_0 = $rs->[0]->[0];
	$sth->finish;
#	print "$stmnt<br/>";
	$wk_1 = $wk_0 - 1;
	$wk_2 = $wk_0 - 2;
	$stmnt = "SELECT CONCAT(MIN(date), ' - ',  MAX(date)) FROM payroll_dates WHERE week_num = '$wk_0'"; 
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $dt_0 = $rs->[0]->[0];
	$sth->finish;
#	print "$stmnt<br/>";
	$stmnt = "SELECT CONCAT(MIN(date), ' - ',  MAX(date)) FROM payroll_dates WHERE week_num = '$wk_1'"; 
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $dt_1 = $rs->[0]->[0];
	$sth->finish;
#	print "$stmnt<br/>";
	$stmnt = "SELECT CONCAT(MIN(date), ' - ',  MAX(date)) FROM payroll_dates WHERE week_num = '$wk_2'"; 
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $dt_2 = $rs->[0]->[0];
	$sth->finish;
#	print "$stmnt<br/>";
	my @tmp = ($wk_0, $dt_0); 
	push @weeks, [@tmp];
	@tmp = ($wk_1, $dt_1); 
	push @weeks, [@tmp];
	@tmp = ($wk_2, $dt_2); 
	push @weeks, [@tmp];
#	print "OLA $weeks[0][0] - $weeks[0][1] : $weeks[1][0] - $weeks[1][1] : $weeks[2][0] - $weeks[2][1] <br/>";
	return \@weeks;
}






sub get_payroll_week_of_interest
{
	my $week = $_[0];
	$stmnt = "SELECT MIN(date), MAX(date), MIN(pay_num) FROM payroll_dates WHERE week_num = '$week'";
#	print "$stmnt <br/>"; 
	& run_statement ($stmnt);
   @woi = $sth->fetchrow;
   $sth->finish;
   return \@woi;
}

sub get_current_pay_period
{
	$stmnt = "SELECT pay_num FROM payroll_dates WHERE date = CURDATE()";		
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $pay = $rs->[0]->[0];
	$sth->finish;
	return $pay;
}

sub update_standard_time 
{
   $cli = "$_[0]";
   $id = "$_[1]";
   $std= "$_[2]";
   $stmnt = "UPDATE tasks SET std_min = $std WHERE chrg_code = '$id' AND client_id = '$cli'";
   & run_statement ($stmnt);
   $sth->finish;
}

sub update_standard_rate 
{
   $cli = "$_[0]";
   $id = "$_[1]";
   $rate= "$_[2]";
   $stmnt = "UPDATE tasks SET rate = $rate WHERE chrg_code = '$id' AND client_id = '$cli'";
   & run_statement ($stmnt);
   $sth->finish;
}

sub update_master_standard_time
{
   $id = "$_[0]";
   $std= "$_[1]";
   $stmnt = "UPDATE master_tasks SET std_min = $std WHERE chrg_code = '$id'";
   & run_statement ($stmnt);
   $sth->finish;
}

sub get_pay_events # should be redundant
{
   my $loc = $_[0];
   my @tmp=();
   my @pay=();
   my $stmnt = "SELECT PH.id, CONCAT(E.first_name,' ',E.last_name) AS Name, DE.employee_num, PH.date AS Date, 
   SEC_TO_TIME(SUM(TIME_TO_SEC(DE.elapsed))) as 'Clock_Time', PH.hours AS 'Pay_Hrs', DAYNAME(PH.date), 
   MONTHNAME(PH.date), DAY(PH.date), YEAR(PH.date), TIME(MIN(DE.start)) as start, TIME(MAX(DE.end)) as end, 
   PH.comment, (PH.cli_min/PH.tot_min)*100 AS Util,  
   FROM day_events AS DE 
   JOIN employees AS E ON (DE.employee_num=E.employee_num) 
   JOIN payroll_hrs AS PH ON (PH.date =  DATE(DE.start)) 
   WHERE DATE(DE.start) = PH.date AND 
   DE.employee_num = PH.employee_num AND 
   PH.hours IS NULL AND E.location = '$loc' 
   GROUP BY DATE(DE.start), DE.employee_num ORDER BY DATE(DE.start)";
   # print "$stmnt<br/>";
   & run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) 
   {
      push @pay, [@tmp];
   }
   $sth->finish;
   return \@pay;
}

sub check_for_weekend
{
   my $payDate = $_[0];
   my $stmnt = "SELECT DAYOFWEEK('$payDate')";
   & run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $dow = $rs->[0]->[0];
	if ( $dow == 1 || $dow == 7 ) {
		$sw = 0;
	} else {
		$sw = 1;
	}		
	return $sw;
}

sub add_active_employees_to_payroll
{
   my $loc = $_[0];
   my $payDate = $_[1];
   my $stmnt = "INSERT INTO payroll_hrs (employee_num, date, source) 
	SELECT employee_num, '$payDate', 1 from employees 
	WHERE active = 'Y' 
	AND job IN ('WHS', 'CSR') 
	AND location = '$loc' 
	AND employee_num NOT IN (select employee_num FROM payroll_hrs WHERE date = '$payDate')";
   & run_statement ($stmnt);
#   print "$stmnt <br/>";
   $sth->finish;
}


sub 	update_start_end_elapsed_for_payroll_hrs
{
   my $loc = $_[0];
   my $payDate = $_[1];
	# update start time for day
   my $stmnt = "UPDATE payroll_hrs PH 
   JOIN employees AS E ON(E.employee_num = PH.employee_num) 
   SET PH.start = (SELECT MIN(DE.start) FROM day_events DE WHERE DATE(DE.start) = '$payDate' AND DE.employee_num = PH.employee_num) 
   WHERE PH.date = '$payDate' AND E.location = '$loc'";
   & run_statement ($stmnt);
   $sth->finish;
	# update end time for day
   my $stmnt = "UPDATE payroll_hrs PH 
   JOIN employees AS E ON(E.employee_num = PH.employee_num) 
   SET PH.end = (SELECT MAX(DE.end) FROM day_events DE WHERE DATE(DE.start) = '$payDate' AND DE.employee_num = PH.employee_num) 
   WHERE PH.date = '$payDate' AND E.location = '$loc'";
   & run_statement ($stmnt);
   $sth->finish;
	# update elapsed time for day
   my $stmnt = "UPDATE payroll_hrs PH 
   JOIN employees AS E ON(E.employee_num = PH.employee_num)
   SET PH.elapsed = (SELECT SEC_TO_TIME (SUM(TIME_TO_SEC(DE.elapsed))) FROM day_events DE WHERE DATE(DE.start) = PH.date AND DE.employee_num = PH.employee_num) 
   WHERE PH.date = '$payDate' AND E.location = '$loc'";
   & run_statement ($stmnt);
   $sth->finish;
   # update lunch time 
   my $stmnt = "UPDATE ttraker.payroll_hrs AS PH 
	JOIN ttraker.employees AS E ON (E.employee_num = PH.employee_num)
	set lunch = 
	(
	SELECT SEC_TO_TIME(SUM(JE.elapsed)*60)  
	from ttraker.job_events JE
	JOIN ttraker.job_headers JH ON (JH.job_num = JE.job_num)
	WHERE JH.client_id = 'LUNCH' AND JE.pay_date = PH.date
	AND JH.employee_num = PH.employee_num 
	) 
	WHERE PH.date = '$payDate' AND E.location = '$loc'";
   & run_statement ($stmnt);
   $sth->finish;
   
  	my $stmnt = "UPDATE ttraker.payroll_hrs AS PH 
	JOIN ttraker.employees AS E ON (E.employee_num = PH.employee_num)
	set lunch = '00:00:00' WHERE PH.date = '$payDate' AND E.location = '$loc' AND PH.lunch IS NULL";
   & run_statement ($stmnt);
   $sth->finish;   

	# update net time
   my $stmnt = "UPDATE ttraker.payroll_hrs AS PH
   JOIN ttraker.employees AS E ON (E.employee_num = PH.employee_num)
   SET PH.net_time = TIMEDIFF(PH.elapsed, PH.lunch) WHERE
	PH.date = '$payDate' AND E.location = '$loc'";
   & run_statement ($stmnt);
   $sth->finish;

   # update week_num and pay_num;
   my $stmnt = "UPDATE ttraker.payroll_hrs AS PH
   JOIN ttraker.payroll_dates AS PD ON (PH.date = PD.date)
   SET PH.week_num = PD.week_num, PH.pay_num = PD.pay_num WHERE
	PH.date = '$payDate' AND PH.pay_num IS NULL";
   & run_statement ($stmnt);
   $sth->finish;
}

sub update_job_event_elapsed_adj 
{
	my $loc = $_[0];
	my $date = $_[1];
	my $stmnt = "UPDATE job_events AS JE 
	JOIN job_headers AS JH ON (JH.job_num = JE.job_num)  
	JOIN employees AS E ON (E.employee_num = JH.employee_num)
	JOIN payroll_hrs AS PH ON (PH.employee_num = JH.employee_num)
	SET JE.elapsed_adj = JE.elapsed*PH.time_adj 
	WHERE PH.date = JE.pay_date AND
	E.location = '$loc' AND JE.pay_date = '$date'";
	run_statement ($stmnt);
	my $stmnt = "UPDATE job_events AS JE 
	JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
	JOIN employees AS E ON (E.employee_num = JH.employee_num)  
	SET JE.lab_cost = JE.elapsed*JH.rate/60,
	JE.lab_cost_adj = JE.elapsed_adj*JH.rate/60
	WHERE E.location = '$loc' AND JE.pay_date = '$date'";
	run_statement ($stmnt);
	$sth->finish;
}

sub update_job_headers_time_cost
{
	my $loc = $_[0];
	my $date = $_[1];
	my @jobs = ();
	my $stmnt = "SELECT job_num FROM job_events WHERE pay_date = '$date' GROUP BY job_num";
	run_statement ($stmnt);
	while (($tmp) = $sth->fetchrow) {
		push @jobs, $tmp;
	}
	foreach my $job (@jobs) {
		$stmnt = "UPDATE job_headers SET 
				act_min = ( SELECT SUM(elapsed) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				act_min_adj = ( SELECT SUM(elapsed_adj) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				lab_cost_adj = ( SELECT SUM(lab_cost_adj) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				std_min = ( SELECT SUM(std_time) FROM task_events WHERE job_num = $job AND active = 'Y'),
				revenue = ( SELECT SUM(revenue) FROM task_events WHERE job_num = $job AND active = 'Y')
				WHERE job_num = $job";
		run_statement ($stmnt);
#		print "$stmnt<br/>";
	}
	$sth->finish;
}

sub get_payroll_events
{
	my $loc = $_[0];
	my $payDate = $_[1];
	my @tmp=();
	my @pay=();
	my $stmnt = "SELECT PH.id, CONCAT(E.first_name,' ',E.last_name), 
	E.employee_num, PH.elapsed, TIME_TO_SEC(PH.lunch)/60, TIME_TO_SEC(PH.net_time)/3600, 
	PH.start, PH.end,	PH.comment, (PH.cli_min/PH.tot_min)*100, 
	PH.hours, PH.reg, PH.ot1, PH.ot2, PH.vac, PH.sta, PH.sic, PH.brv, PH.abs, PH.loa  
	FROM employees AS E 
	LEFT JOIN day_events AS DE ON (E.employee_num = DE.employee_num) 
	JOIN payroll_hrs AS PH ON (PH.employee_num = E.employee_num) 
	WHERE E.location = '$loc' 
	AND PH.date = '$payDate' 
	GROUP BY E.employee_num ORDER BY PH.source, E.employee_num";
   & run_statement ($stmnt);
#  print "$stmnt<br/>";
   while ((@tmp) = $sth->fetchrow) 
   {
      push @pay, [@tmp];
   }
   $sth->finish;
   return \@pay;
}

sub update_daily_payroll_hours
{
	my $id = $_[0];
	my $hrs = $_[1];
	my $reg = $_[2];
	my $ot1 = $_[3];
	my $ot2 = $_[4];
	my $vac = $_[5];
	my $sta = $_[6];
	my $sic = $_[7];
	my $brv = $_[8];
	my $abs = $_[9];
	my $loa = $_[10];
	my $wrk = $reg+$ot1+$ot2;
	$stmnt =	"UPDATE payroll_hrs SET worked='$wrk', hours='$hrs',reg='$reg',ot1='$ot1',ot2='$ot2',vac='$vac',sta='$sta',sic='$sic',brv='$brv',
  					abs='$abs',loa='$loa', oth_min = ($vac + $sta + $sic + $brv)*60  WHERE id='$id'";
 	run_statement ($stmnt);
#	print "$stmnt<br/>"; 
	if ( $hrs > 0 ) {
		$stmnt =	"UPDATE payroll_hrs SET time_adj = (1 + (hours - (TIME_TO_SEC(net_time)/3600))/(TIME_TO_SEC(net_time)/3600)) WHERE id='$id'";
 		run_statement ($stmnt);
	} 	
	$sth->finish;
#	print "$stmnt<br/>";
}


sub adjust_daily_payroll_hours 
{
	my $num = $_[0];
	my $hrs = $_[1];
	my $reg = $_[2];
	my $ot1 = $_[3];
	my $ot2 = $_[4];
	my $vac = $_[5];
	my $sta = $_[6];
	my $sic = $_[7];
	my $brv = $_[8];
	my $abs = $_[9];
	my $loa = $_[10];
	my $date = $_[11];
	$stmnt =	"UPDATE payroll_hrs SET hours='$hrs',reg='$reg',ot1='$ot1',ot2='$ot2',vac='$vac',
	sta='$sta',sic='$sic',brv='$brv', abs='$abs',loa='$loa', 
	time_adj = (1 + ('$hrs' - (TIME_TO_SEC(net_time)/3600))/(TIME_TO_SEC(net_time)/3600)) 
	WHERE employee_num='$num' AND date='$date'";
 	& run_statement ($stmnt);
	$sth->finish;
	my $stmnt = "UPDATE job_events AS JE 
	JOIN job_headers AS JH ON (JH.job_num = JE.job_num)  
	JOIN employees AS E ON (E.employee_num = JH.employee_num)
	JOIN payroll_hrs AS PH ON (PH.employee_num = JH.employee_num)
	SET JE.elapsed_adj = JE.elapsed*PH.time_adj 
	WHERE PH.date = JE.pay_date AND
	E.employee_num = '$num' AND JE.pay_date = '$date'"; 
   & run_statement ($stmnt);
   $sth->finish;

}

sub update_weekly_payroll_employee_hours
{
	my $num = $_[0];
	my $hrs = $_[1];
	my $reg = $_[2];
	my $ot1 = $_[3];
	my $ot2 = $_[4];
	my $vac = $_[5];
	my $sta = $_[6];
	my $sic = $_[7];
	my $brv = $_[8];
	my $abs = $_[9];
	my $loa = $_[10];
	my $start = $_[11]; 
	my $end = $_[12];
	my $weekNum = $_[13];
	my $payNum = $_[14];
	my $now = $_[15];
#	print "NOW IS $now<br/>";	
	$stmnt = "select COUNT(*) from payroll_weeks WHERE employee_num = '$num' AND week_num = '$weekNum'";
#	print "$stmnt <br/>";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$count = $rs->[0]->[0];
	$sth->finish;
	if ($count == 0 ) {
	   
		$stmnt =	"INSERT INTO payroll_weeks (employee_num,hours,reg,ot1,ot2,vac,sta,sic,brv,abs,loa,start,end,week_num,pay_num,stamp)
					VALUES ('$num','$hrs','$reg','$ot1','$ot2','$vac','$sta','$sic','$brv','$abs','$loa','$start','$end','$weekNum','$payNum','$now')";
		& run_statement ($stmnt);
		$sth->finish;
#		print "COUNT = 0! $stmnt <br/>";
	} elsif ( $count == 1 ) {
		$stmnt =	"UPDATE payroll_weeks SET employee_num='$num',hours='$hrs',reg='$reg',ot1='$ot1',ot2='$ot2',vac='$vac',sta='$sta',sic='$sic',brv='$brv',
  					abs='$abs',loa='$loa',stamp='$now' WHERE employee_num='$num' AND week_num='$weekNum'";
 		& run_statement ($stmnt);
		$sth->finish;
#		print "COUNT = 1! $stmnt <br/>";
	} else {
		print "ERROR TOO MANY ENTRIES IN payroll_weeks for $num and $weekNum!<br/>";
	}
}



sub get_pay_week_dates
{
	my $week = $_[0];
	$stmnt = "SELECT DATE_FORMAT((FROM_DAYS(734504+$week*7)),'%a %b %d')";
   & run_statement ($stmnt);
   my $start = $sth->fetchrow;
   $sth->finish;
	$stmnt = "SELECT DATE_FORMAT((FROM_DAYS(734504+$week*7+6)),'%a %b %d, %Y')";
   & run_statement ($stmnt);
   my $end = $sth->fetchrow;
   $sth->finish;
	my $str = $start . " thru " . $end;	
	return $str;
}

sub get_payroll_weekly_list
{
	my $week = $_[0];
   my $loc = $_[1];
   my @weekly_pay = ();
	$stmnt = "SELECT PH.employee_num, CONCAT(E.first_name,' ',E.last_name) AS Name, SUM(PH.hours), SUM(PH.reg), SUM(PH.ot1), 
	SUM(PH.ot2), SUM(PH.vac), SUM(PH.sta), SUM(PH.sic), SUM(PH.brv), SUM(PH.abs), SUM(PH.loa) FROM payroll_hrs AS PH 
	JOIN employees AS E ON (PH.employee_num = E.employee_num) 
	WHERE week_num = $week AND E.location = '$loc' GROUP BY employee_num ORDER BY source, employee_num";
   & run_statement ($stmnt);
#	print "$stmnt<br/>";
   while ((@tmp) = $sth->fetchrow) 
   {
      push @weekly_pay, [@tmp];
   }
   $sth->finish;
   return \@weekly_pay;	
}


sub  get_weekly_time_detail
{
	my $num = $_[0];
   my $week = $_[1];
	# print "getting data for $num $week</br>";
	my @weekly_detail = ();
   my @tmp=();
	$stmnt = "SELECT PH.hours,PH.reg,PH.ot1,PH.ot2,PH.vac,PH.sta,PH.sic,PH.brv,PH.abs,PH.loa, PH.date, PH.employee_num 
	FROM payroll_hrs AS PH 
	WHERE PH.week_num = '$week' AND PH.employee_num = '$num' 
	ORDER BY PH.date";
   & run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) 
   {
      push @weekly_detail, [@tmp];
   }
   $sth->finish;
#	print "$stmnt<br/>";
   return \@weekly_detail;
}

sub get_weekly_time_totals

{
	my $num = $_[0];
	my $week = $_[1];
	# print "getting data for $num $week</br>";
	my @weekly_totals = ();
	$stmnt = "SELECT SUM(PH.hours),SUM(PH.reg),SUM(PH.ot1),SUM(PH.ot2),SUM(PH.vac),SUM(PH.sta),SUM(PH.sic),SUM(PH.brv),SUM(PH.abs),SUM(PH.loa) 
				FROM payroll_hrs AS PH 
				WHERE PH.week_num = '$week' AND PH.employee_num = '$num'";
	run_statement ($stmnt);
	@weekly_totals = $sth->fetchrow;
	return \@weekly_totals;
}


sub update_job_headers_with_time
{
	my $job = $_[0];
	my $stmnt = "UPDATE job_headers SET 
				act_min = ( SELECT SUM(elapsed) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				act_min_adj = ( SELECT SUM(elapsed_adj) FROM job_events WHERE  job_num = $job AND active = 'Y'),
				std_min = ( SELECT SUM(std_time) FROM task_events WHERE job_num = $job AND active = 'Y'),
				revenue = ( SELECT SUM(revenue) FROM task_events WHERE job_num = $job AND active = 'Y')
				WHERE job_num = $job";
	run_statement ($stmnt);
	$stmnt = "UPDATE job_headers SET lab_cost_adj = act_min_adj*(rate/60) WHERE job_num = $job";
	run_statement ($stmnt);
	$sth->finish;
}

sub add_msg_payroll_hrs
{
   my $num = $_[0];
   my $date = $_[1];
	my $reas = $_[2];
	if ($reas == 1 ) {
		$msg = "log out";
	} elsif ( $reas == 2 ) {
		$msg = "";
		my $log_str = "EMPLOYEE $num WORKED LONG HOURS ON $date!";
		print_log ($log_str)
	}
	$stmnt = "UPDATE payroll_hrs SET comment = '$msg' WHERE employee_num = $num AND date = '$date'";
   & run_statement ($stmnt);
   $sth->finish;
}

sub log_out_of_work
{
	my $num = "$_[0]";
	my $now = "$_[1]";
	my $dir = "$_[2]";
	if ( $dir = "" ) {
		$dir = "NA";
	}
#	my $date = get_today();
	pause_current_job ($num, $now, $dir);    
	my $stmnt = "SELECT day_event_num FROM logged_in WHERE employee_num = $num";
	run_statement ($stmnt);
	my $day_num = $sth->fetchrow;
	$sth->finish;
	calc_elapsed_day_time ( $day_num, $now);
	update_daily_time_stats ($num,"");
	$stmnt = "DELETE FROM logged_in WHERE day_event_num = $day_num";
	run_statement ($stmnt);
	$sth->finish;
}

sub log_break_time
{
   	my $num = "$_[0]";
   	my $now = "$_[1]";
	my $dir = "$_[2]";
	if ( $dir = "" ) {
		$dir = "NA";
	}
   pause_current_job ($num, $now, $dir);  	
   my $job_num = get_new_count(JOB);
#   my $task_num = get_new_count(TASK);
   my $stmnt = "INSERT INTO job_headers (job_num, doc_num , client_id, employee_num, stamp) VALUES ( $job_num, NULL, 'BREAK', $num, '$now')";
   & run_statement ($stmnt);
   $sth->finish;
   $stmnt = "INSERT INTO job_open (doc_num , job_num, employee_num, client_id, stamp) VALUES ( NULL, $job_num, $num, 'BREAK', '$now')";
   & run_statement ($stmnt);
   $sth->finish;
}

sub get_open_job_count
{
   my $num = $_[0];
   my $stmnt = "SELECT COUNT(*) from job_open where employee_num = $num";
   & run_statement ($stmnt);
   my $open_job_count = $sth->fetchrow;
   $sth->finish;
   return $open_job_count;
}

sub pause_current_job
{
#	print "LOOKING AT PAUSING CURRENT JOB!<br/>";	
	my $num= $_[0];
	my $now= $_[1];
	my $dir= $_[2];
	if ( $dir = "" ) {
		$dir = "NA";
	}
   my $cur_count = get_open_job_count ($num);
   my $stmnt;
   if ( $cur_count > 0 ) {
      $stmnt = "SELECT session, doc_num, job_num, employee_num, client_id, stamp, dir FROM job_open WHERE employee_num = '$num'";
      & run_statement ($stmnt);
      my ($session, $doc, $job, $emp, $cli, $start, $dir) = $sth->fetchrow;  
      $sth->finish;
      if ( $cli eq "LUNCH" || $cli eq "BREAK" || $cli eq "IDLE"  ) {
		# $dir = "NA";
		# do nothing ie do not put lunch, break or idle on hold      	
      } elsif ( $cli eq "" ) { # this should not be possible
         $stmnt = "INSERT INTO job_hold (session, doc_num, job_num, employee_num, client_id, dir) VALUES ($session, $doc, '$job', '$emp', NULL, '$dir')";
         & run_statement ($stmnt);
         $sth->finish;
      } elsif ( $doc eq "" ) {
         $stmnt = "INSERT INTO job_hold (session, doc_num, job_num, employee_num, client_id, dir) VALUES ($session, NULL, '$job', '$emp', '$cli', '$dir')";
         & run_statement ($stmnt);
         $sth->finish;
      } else {
         $stmnt = "INSERT INTO job_hold (session, doc_num, job_num, employee_num, client_id, dir) VALUES ($session, $doc, '$job', '$emp', '$cli', '$dir')";
         & run_statement ($stmnt);
         $sth->finish;
      }   
      my $elapsed = calc_elapsed_minutes ( $start, $now);
      $stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj, dir)  VALUES ($job, DATE('$now'), '$start', '$now', '$elapsed','$elapsed', '$dir')";
      run_statement ($stmnt);
#		print "CREATING JOB EVENT $stmnt<br/>";	
      $sth->finish;
	if ( $cli eq "LUNCH") {			
		$stmnt = "UPDATE job_events AS JE 
				SET JE.lab_cost = 0, JE.lab_cost_adj = 0
				WHERE JE.job_num = $job";
	} else {	
		$stmnt = "UPDATE job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				SET JE.lab_cost = (EM.rate/60)*JE.elapsed, JE.lab_cost_adj = (EM.rate/60)*JE.elapsed
				WHERE JE.job_num = $job";
	}
	run_statement ($stmnt);
	$sth->finish;
    close_open_jobs ($num)
   }
}


sub log_lunch_break
{
   my $num = "$_[0]";
   my $now = "$_[1]";
   pause_current_job ($num, $now);  	
   my $job_num = get_new_count(JOB);
#   $task_num = get_new_count(TASK);
   my $stmnt = "INSERT INTO job_headers (job_num, doc_num , client_id, employee_num, stamp) VALUES ( $job_num, NULL, 'LUNCH', $num, '$now')";
   & run_statement ($stmnt);
   $sth->finish;  
   $stmnt = "INSERT INTO job_open (doc_num , job_num, employee_num, client_id, stamp) VALUES ( NULL, $job_num, $num, 'LUNCH', '$now')";
   & run_statement ($stmnt);
   $sth->finish;
}

sub get_now
{
	my $stmnt = "SELECT now()";
   run_statement ($stmnt);
   my $now = $sth->fetchrow;
   $sth->finish;
   return $now;
}

sub get_local_time
{
	my $loc = "$_[0]";
	my $stmnt = "SELECT offset FROM time_zone WHERE location = '$loc'";
   run_statement ($stmnt);
   my $off = $sth->fetchrow;
#	print "$stmnt  --->  $off<br/>";
	$stmnt = "SELECT CONVERT_TZ(NOW(),'+0:00','$off')";
   run_statement ($stmnt);
   my $now = $sth->fetchrow;
#	print "$stmnt  --->  $now<br/>";
   $sth->finish;
   return $now;
}

sub get_today
{
	my $stmnt = "SELECT curdate()";
	run_statement ($stmnt);
	my $today = $sth->fetchrow;
	$sth->finish;
	return $today;
}

sub close_current_job
{
   my $job= $_[0];
   my $stmnt = "DELETE FROM job_open WHERE job_num = $job";
   & run_statement ($stmnt);
   $sth->finish;
}

sub close_open_jobs
{
   my $empNum= $_[0];
   my $stmnt = "DELETE FROM job_open WHERE employee_num = $empNum";
   & run_statement ($stmnt);
   $sth->finish;
}

sub get_new_count 
{
   my $ID = $_[0];
   $stmnt = "SELECT count FROM count_holder WHERE id = '$ID'";
   run_statement ($stmnt);
   my $count = $sth->fetchrow;
   my $next = $count + 1;
   $stmnt = "UPDATE count_holder SET count ='$next' WHERE id = '$ID'";
   & run_statement ($stmnt);
   $sth->finish;
   return $count;
}


sub add_new_temp_employee
{
	my $tmpFirst = $_[0];
	my $tmpLast = $_[1];
	my $tmpLogin = $_[2];
	my $tmpLocation = $_[3];
	my $tmpFacility  = $_[4];
	my $tmpAgency  = $_[5];
	my $tmpRate = $_[6];
	my $tmpNum = get_new_count (TEMP);
#	my $tmpNum = get_new_temp_employee_num ($tmpLogin);
	my $now = get_now();
	my $stmnt = "INSERT INTO employees (employee_num, first_name, last_name, location, facility, agency, job, pay_cat, rate, log_id, active, stamp) 
	VALUES ( $tmpNum, '$tmpFirst', '$tmpLast', '$tmpLocation', '$tmpFacility', '$tmpAgency', 'TMP', 'HR', '$tmpRate', '$tmpLogin', 'Y', '$now')";
   & run_statement ($stmnt);
   $sth->finish;
}

sub update_start_job_open
{
   my $job = $_[0];
   my $now = $_[1];
#   my $new_task_num = get_new_count(TASK);
   my $stmnt = "UPDATE job_open SET stamp = '$now'  WHERE job_num = '$job'";
   & run_statement ($stmnt);
   $sth->finish;
   return $new_task_num;
}

sub get_status
{
   my $num = $_[0];
   my $date = $_[1];
   my $stmnt = "select COUNT(*) from logged_in where employee_num = $num and DATE(start) = '$date'";
   run_statement ($stmnt);
   $rs = $sth->fetchall_arrayref();
   my $log_count = $rs->[0]->[0];
   $sth->finish;
#   print "   LOG_COUNT = $log_count<br/>";
   $stmnt = "select COUNT(*) from job_open where employee_num = $num and DATE(stamp) = '$date' and client_id = 'LUNCH'";
   & run_statement ($stmnt);
   $rs = $sth->fetchall_arrayref();
   my $lunch_count = $rs->[0]->[0];
   $sth->finish;   
   if ( $log_count == 1 ) {
	return "Open";
   } elsif ( $lunch_count == 1 ) {
		return "Lunch";
   } else {
		return "ok"
   } 
}

sub get_open_job
{
   my $num = $_[0];  
   my $stmnt = "SELECT doc_num, job_num, employee_num, client_id, stamp from job_open WHERE employee_num = $num";
   & run_statement ($stmnt);
   my @open_job = $sth->fetchrow;
   return \@open_job;
}

sub get_client_count
{
   my $cli = "$_[0]";
   my $stmnt = "SELECT COUNT(*) FROM clients WHERE client_id = '$cli'";
   & run_statement ($stmnt);
   my $cli_count = $sth->fetchrow;
   $sth->finish();
   return $cli_count;
}

sub add_new_client
{
   my $cli = "$_[0]";
   my $loc = "$_[1]";	
   my $name = "$_[2]";
   my $stmnt = "INSERT INTO clients (client_id, facility, client_name, category) VALUES ( '$cli', '$loc', '$name', 10)";  
   & run_statement ($stmnt);
   $sth->finish();
   $stmnt = "INSERT INTO tasks (client_id, chrg_code, description, units, show_whs, show_csr, show_adm, inbound, outbound, other, include, whs_priority, csr_priority, adm_priority, maves_chrg_code, rev_code, std_min, rate) SELECT client_id, chrg_code, description, units, show_whs, show_csr, show_adm, inbound, outbound, other, include, whs_priority, csr_priority, adm_priority, maves_chrg_code, rev_code, std_min, rate FROM master_tasks";
   & run_statement ($stmnt);
   $sth->finish();
   $stmnt = "UPDATE tasks SET client_id = '$cli' WHERE client_id = 'MASTER'";
   & run_statement ($stmnt);
   $sth->finish();
   $stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli'";
   & run_statement ($stmnt);
   my $task_count = $sth->fetchrow;
   $sth->finish();
   return $task_count;
}

sub get_clients
{
   $stmnt = "SELECT client_id, client_name FROM clients WHERE active = 'Y' ORDER BY client_id";
   & run_statement ($stmnt);
   while (($client_id, $client_name) = $sth->fetchrow) {
      push (@clients, $client_id);
   }
   $sth->finish;
   return \@clients;
}

sub get_clients_all
{
	my $stmnt = "SELECT client_id, client_name FROM clients ORDER BY client_id";
	run_statement ($stmnt);
	my ($client_id, $client_name);
	my @ clients = ();
	while (($client_id, $client_name) = $sth->fetchrow) {
      push (@clients, $client_id);
	}
	$sth->finish;
	return \@clients;
}

sub get_clients_not_on_hot_list
{
	my $num = $_[0];
	my $stmnt = "SELECT DISTINCT client_id FROM clients WHERE client_id NOT IN ( SELECT client_id FROM csr_hot_list WHERE employee_num = $num) AND csr_grp > -1";	
	run_statement ($stmnt);
	my @clients = ();
	my $client_id;
	while (($client_id) = $sth->fetchrow) {
		push (@clients, $client_id);
	}
	$sth->finish;
	return \@clients;
}


sub get_client_name
  # THIS IS NOT WORKING
{
   my $cli = $_[0];
   $stmnt = "SELECT client_name FROM clients WHERE client_id = $cli";
   & run_statement ($stmnt);
   my $cli_name = $sth->fetchrow;
   $sth->finish();
   return ($cli_name);
}

sub get_task_count
{
# count the tasks asscociated with a client and a job category
my $cli = $_[0];
my $job_id = $_[1];
my $fld_str = $_[2];
my $stmnt;
if ( $job_id eq "WHS" || $job_id eq "TMP" ) {
   $stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli' AND show_whs = 'Y' AND $fld_str = 'Y' ";
} elsif ( $job_id eq "CSR" ) {
   $stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli' AND show_csr = 'Y' AND $fld_str = 'Y' ";
} else {
   $stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli' AND show_adm = 'Y' AND $fld_str = 'Y' ";
}
& run_statement ($stmnt);
$rs = $sth->fetchall_arrayref();
my $task_count = $rs->[0]->[0];
$sth->finish;
# print "COUNT: $task_count FROM: $stmnt<br/>";   
return $task_count;
}


sub get_daily_time_summary
{
	my $num = $_[0];
	my $date = $_[1];
#	print "LIB GETTING SUMMARY FOR $num $date<br/>";
	my @jobs=();
#	print "NUM: $num DATE: $date<br/>";
	my $stmnt = "SELECT JH.client_id AS Client, SEC_TO_TIME(SUM(JE.elapsed*60)) AS Elapsed FROM job_events AS JE JOIN job_headers AS JH ON (JE.job_num = JH.job_num) JOIN clients AS C ON (C.client_id = JH.client_id) WHERE JH.employee_num = $num AND JE.pay_date = '$date' AND C.category > 0 GROUP BY JH.client_id ORDER BY Elapsed DESC";
	# print "$stmnt<br/>";
	& run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @jobs, [@tmp];
	}
	$sth->finish;
	return \@jobs;
}

sub get_daily_time_detail
{
	my $num = $_[0];
	my $date = $_[1];
	my @jobs=();
#	print "NUM: $num DATE: $date<br/>";
	my $stmnt = "SELECT JH.client_id AS Client, JE.job_num, JH.doc_num, TIME(JE.start) AS Start ,
				TIME(JE.end) AS End, SEC_TO_TIME(JE.elapsed*60) AS Elapsed FROM job_events 
				AS JE JOIN job_headers AS JH ON (JE.job_num = JH.job_num) 
				WHERE JH.employee_num = $num AND JE.pay_date = '$date' ORDER BY JE.start";
#	print "$stmnt<br/>";
	& run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @jobs, [@tmp];
	}
	$sth->finish;
	return \@jobs;
}


sub update_daily_from_payroll_list 
{
	my $loc = $_[0];
	my $date = $_[1];
	my $stmnt = "SELECT employee_num FROM payroll_hrs WHERE date = '$date' GROUP BY employee_num";
	run_statement ($stmnt);
	while (($tmp) = $sth->fetchrow) {
		push @employees, $tmp;
	}
	foreach my $emp (@employees) {
		update_daily_time_stats ($emp, $date)
	}
}

sub update_daily_time_stats
{
	my $num = $_[0];
	my $date = $_[1];
	my $stmnt;
	if ( $date eq "") {
		$stmnt = "SELECT DATE(start) FROM logged_in WHERE employee_num = $num";
		run_statement ($stmnt);
		$date = $sth->fetchrow; 
		$sth->finish;
	}	
	$stmnt = "SELECT SUM(JE.elapsed) FROM job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN clients AS C ON (C.client_id = JH.client_id) 
				WHERE pay_date = '$date' AND JH.employee_num = $num AND C.category > 0";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$tot_min = $rs->[0]->[0];
	if ($tot_min eq "" ) { 
		$tot_min = 0;
	}	
	$stmnt = "SELECT SUM(JE.elapsed) FROM job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN clients AS C ON (C.client_id = JH.client_id) 
				WHERE	pay_date = '$date' AND JH.employee_num = $num AND C.category = 10";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$cli_min = $rs->[0]->[0];
	if ($cli_min eq "" ) { 
		$cli_min = 0;
	}	
	$stmnt = "SELECT SUM(JE.elapsed) FROM job_events AS JE 
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
				JOIN clients AS C ON (C.client_id = JH.client_id) 
				WHERE pay_date = '$date' AND JH.employee_num = $num 
				AND C.category = 8";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$rs = $sth->fetchall_arrayref();
	$mck_min = $rs->[0]->[0];
	if ($mck_min eq "" ) { 
		$mck_min = 0;
	}	
	$stmnt = "UPDATE payroll_hrs SET tot_min = $tot_min, cli_min = $cli_min, mck_min=$mck_min, oth_min = (vac+sta+sic+brv)*60 
				WHERE date = '$date' and employee_num = $num";
	run_statement ($stmnt);
	$sth->finish;
	print_log ($stmnt);
#	$stmnt 	=	"UPDATE payroll_hrs SET oth_min = (vac+sta+sic+brv)*60
#				WHERE date = '$date' and employee_num = $num";
#	run_statement ($stmnt);
#	print "$stmnt<br/>";
#	print_log ($stmnt)
	$sth->finish;
}

sub get_adm_task_count
{
# count the tasks asscociated with a client for std time adjustment
my $cli = $_[0];
$stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli'";
run_statement ($stmnt);
$rs = $sth->fetchall_arrayref();
$task_count = $rs->[0]->[0];
$sth->finish;
#print "<br/>THE TASK COUNT IS $task_count for $cli<br/>";
return $task_count;
}

sub get_paused_job_count
{
my $num = $_[0];
$stmnt = "SELECT COUNT(*) from job_hold where employee_num = $num";
run_statement ($stmnt);
$rs = $sth->fetchall_arrayref();
$job_count = $rs->[0]->[0];
$sth->finish;
return $job_count;
}

sub get_paused_jobs
{
my $num = $_[0];
my @jobs=();
# print "EMP NUM = $num<br/>";
$stmnt = "SELECT id, doc_num, job_num, client_id FROM job_hold WHERE employee_num = $num";
run_statement ($stmnt);
while (($id, $doc_num, $job_num, $client_id) = $sth->fetchrow) {
#   print "$doc_num, $job_num, $client_id<br/>";
   if ( $doc_num eq "NULL" ) {
      $str= "<option value=$id >$client_id : ------ </option>";
   } elsif ( $client_id eq"" ) {
      $str= "<option value=$id >------ : $doc_num </option>";
   } else {
      $str= "<option value=$id >$client_id : $doc_num </option>";
   }
#   print "THE STRING $str <br/><br/>";  
   push (@jobs, $str); 
};
$sth->finish;
return \@jobs;
}

sub get_message
{
my $num = $_[0];
# we need to get information from the database regarding current activity and paused jobs
my $open_count = get_open_job_count ($num);
my $paused_count = get_paused_job_count ($num);
my $doc_num, $client_id, $txt;
my $msg = "";
if ( $open_count > 0 ) {
   my $open = get_open_job ($num);
   $msg = "Current Activity: @$open[3] @$open[0]<br/><br/>"
}
if ( $paused_count > 0 ) {
   my $stmnt = "SELECT doc_num, client_id FROM job_hold WHERE employee_num = $num";
   & run_statement ($stmnt);
   my $txt = "";
   while (($doc_num, $client_id) = $sth->fetchrow) {
      $txt = "$txt" . "Paused: $client_id $doc_num<br/>"; 
   }
   $sth->finish;
   $msg = "$msg $txt<br/>"
}
return $msg;
}

sub get_session_message
{
my $num = $_[0];
my $session = $_[1];
# we need to get information from the database regarding current activity and paused jobs
my $open_count = get_open_job_count ($num);
my $paused_count = get_paused_job_count ($num);
my $doc_num, $client_id, $txt;
my $msg = "";
if ( $open_count > 0 ) {
   my $open = get_open_job ($num);
   $msg = "Active: @$open[3] @$open[0]<br/><br/>"
}
if ( $paused_count > 0 ) {
   my $stmnt = "SELECT doc_num, client_id FROM job_hold WHERE employee_num = $num AND session = $session";
   & run_statement ($stmnt);
   my $txt = "";
   while (($doc_num, $client_id) = $sth->fetchrow) {
      $txt = "$txt" . "Created: $client_id $doc_num<br/>"; 
   }
   $sth->finish;
   $msg = "$msg $txt<br/>"
}
return $msg;
}



sub get_disabled_list 
{
   my $num = $_[0];
   my @dis = ("", "", "", "", "", "", "", "", "","");
   my $open_count = get_open_job_count ($num);
   my $paused_count = get_paused_job_count ($num);
   my $open_job_ref = get_open_job ($num);
   my @open = @$open_job_ref;
   my $cli= $open[3];
	my $hold_match = get_hold_client_count ($num, $cli);
	
   if ( $open_count == 0 ) {
      $dis[2]="disabled=\"disabled\"";
      $dis[3]="disabled=\"disabled\"";
      $dis[8]="disabled=\"disabled\"";
   }
   if ( $paused_count == 0 ) {
      $dis[4]="disabled=\"disabled\"";	
   }
   if ( $hold_match == 0 ) {
      $dis[8]="disabled=\"disabled\"";
   }
   if ( $cli eq "LUNCH" ) {
     $dis[2]="disabled=\"disabled\"";
     $dis[3]="disabled=\"disabled\"";
     $dis[5]="disabled=\"disabled\"";
     $dis[8]="disabled=\"disabled\"";
   }
   if ( $cli eq "BREAK" ) {
      $dis[2]="disabled=\"disabled\"";
      $dis[3]="disabled=\"disabled\"";
      $dis[6]="disabled=\"disabled\"";
      $dis[8]="disabled=\"disabled\"";
   }
   if ( $cli eq "IDLE" ) {
      $dis[2]="disabled=\"disabled\"";
      $dis[3]="disabled=\"disabled\"";
      $dis[8]="disabled=\"disabled\"";
   }
   return \@dis;
}

sub get_dimmed_list 
{
   # generate dimmed activity list elements depending on the current activity and the status of paused jobs
   my $num = $_[0];
   # create an array where all elements are "en" - enabled
   # that will be applied to the activity list elements
   my @col = ("en","en","en","en","en","en","en","en","en","en"); 
   my $open_count = get_open_job_count ($num);
   my $paused_count = get_paused_job_count ($num);
   my $open_job_ref = get_open_job ($num);
   my @open = @$open_job_ref;
   my $cli= $open[3];
   my $hold_match = get_hold_client_count ($num, $cli);
   
   
   if ( $open_count == 0 ) {
      $col[2]="dis";
      $col[3]="dis";
   }
   if ( $paused_count == 0 ) {
      # no paused jobs - disable the continue button
      $col[4]="dis";
   }
      if ( $hold_match == 0 ) {
      $col[8]="dis";
   }
   if ( $cli eq "LUNCH" ) {
      # on lunch - disable the finish Jobs, Log Tasks and Lunch buttons
      $col[2]="dis";
      $col[3]="dis";
      $col[5]="dis";
   }
   if ( $cli eq "BREAK" ) {
      # on break - disable the finish Jobs, Log Tasks and Lunch buttons
      $col[2]="dis";
      $col[3]="dis";
      $col[6]="dis";
   }
   if ( $cli eq "IDLE" ) {
      # on idle - disable the finish Jobs and Log Tasks buttons
      $col[2]="dis";
      $col[3]="dis";
   }
   return \@col;
}

sub get_paused_job_message_old
{
my $num = $_[0];
# print "EMP NUM = $num<br/>";
$stmnt = "SELECT doc_num, client_id FROM job_hold WHERE employee_num = $num";
& run_statement ($stmnt);
while (($doc_num, $client_id) = $sth->fetchrow) {
   $str = "$client_id $doc_num";
   push (@jobs, $str); 
}
$sth->finish;
 return (@jobs);
}

sub log_job_event
{
   my $job_num = $_[0];
   my $now = $_[1];
   $stmnt = "SELECT doc_num, job_num, employee_num, client_id, stamp FROM job_open WHERE job_num = '$job_num'";
   & run_statement ($stmnt);	
   my ($doc, $job, $emp, $cli, $start) = $sth->fetchrow; 
   $elapsed = calc_elapsed_minutes ( $start, $now);
   $stmnt = "INSERT INTO job_events  (job_num, pay_date, start, end, elapsed, elapsed_adj ) 
			VALUES ($job, DATE('$start'), '$start', '$now', '$elapsed','$elapsed')";
   run_statement ($stmnt);
	$stmnt = "UPDATE job_events AS JE 
			JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
			JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
			SET JE.lab_cost = (EM.rate/60)*JE.elapsed, JE.lab_cost_adj = (EM.rate/60)*JE.elapsed	
			WHERE JE.job_num = $job_num";
   	run_statement ($stmnt);
   $sth->finish;
}


sub create_idle_event
{
   my $num = $_[0];
   my $now = $_[1];
   my $open_count = get_open_job_count ($num);
   if ( $open_count == 0 ) {
      $job_num = get_new_count(JOB);
#      $task_num = get_new_count(TASK);      
      $stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp) VALUES ( $job_num, NULL, 'IDLE', $num, '$now')";
      run_statement ($stmnt);
      $sth->finish;
#	  print "$stmnt<br/>";			  
      $stmnt = "INSERT INTO job_open (doc_num, job_num, employee_num, client_id, stamp) VALUES ( NULL, $job_num, $num, 'IDLE', '$now')";
      run_statement ($stmnt);
#	  print "$stmnt<br/>";			  
      $sth->finish;  
   }   
}

sub clear_payroll_temp
{
	$stmnt = "DELETE FROM payroll_temp";
	run_statement ($stmnt);
	$sth->finish;  
}

sub write_payroll_temp_period
{
	my $period = $_[0];
#	print "THE PERIOD IS $period<br/>";
	# generate period totals for all employees that worked during the pay priod 
	my $stmnt = "INSERT INTO payroll_temp 
	(start, end, name, loc, employee_num, p_worked, p_hours, p_reg, p_ot1, p_ot2, p_vac, p_sta, p_sic, p_brv) 
	SELECT PP.start, PP.end, CONCAT(E.first_name, ' ', last_name), F.facility_code, E.employee_num, SUM(PH.worked), SUM(PH.hours), SUM(PH.reg), SUM(PH.ot1), SUM(PH.ot2), SUM(PH.vac), SUM(PH.sta), SUM(PH.sic), SUM(PH.brv) 
	FROM payroll_hrs as PH
	JOIN employees AS E ON (E.employee_num = PH.employee_num) 
	JOIN facility AS F ON (F.facility_num = E.facility)
	JOIN payroll_periods AS PP ON(PP.pay_num = PH.pay_num)
	WHERE E.employee_num > 10 AND PH.pay_num = $period AND E.agency = 'MCK' GROUP BY E.employee_num
	ORDER BY E.pay_cat, E.employee_num";
	& run_statement ($stmnt);
#	print "$stmnt<br/>";
	$sth->finish;
}

sub 	update_payroll_temp_weeks
{
	my $period = $_[0];
	my $tbl = "payroll_temp_week";
    my $stmnt = "DROP TABLE IF EXISTS $tbl";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$stmnt = "CREATE TABLE $tbl (week_num INT(6), employee_num INT(7) PRIMARY KEY, worked DECIMAL(5,2), hours DECIMAL(5,2), reg DECIMAL(5,2), 
			ot1 DECIMAL(5,2), ot2 DECIMAL(5,2), vac DECIMAL(5,2), sta DECIMAL(5,2), sic DECIMAL(5,2), brv DECIMAL(5,2))";
	run_statement ($stmnt);
	$sth->finish;
#	print "LOOKING AT WEEKS FOR PAY $period<br/>";
	my @tmp=();
	my @wk=();
	my $stmnt = "SELECT wk1, wk2 FROM payroll_periods WHERE pay_num = $period";
	run_statement ($stmnt);
	my ($wk1, $wk2) = $sth->fetchrow;
	$sth->finish;
#	print "LOOKING AT WEEK NUMS: $wk1 and $wk2<br/>";
	# update data for week 1
	my $stmnt = "SELECT PH.employee_num, SUM(PH.worked), SUM(PH.hours), SUM(PH.reg), SUM(PH.ot1), SUM(PH.ot2), SUM(PH.vac), 
				SUM(PH.sta), SUM(PH.sic), SUM(PH.brv) FROM payroll_hrs as PH 
				JOIN employees AS E ON (E.employee_num = PH.employee_num) WHERE week_num = $wk1 
				AND E.agency = 'MCK' GROUP BY PH.employee_num";
 #	print "$stmnt<br/>";
	run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @wk, [@tmp];
	}
	$sth->finish;
	for my $i (0..$#wk ) {
		$stmnt = "UPDATE payroll_temp SET w1_worked=$wk[$i][1], w1_hours=$wk[$i][2], w1_reg=$wk[$i][3], w1_ot1=$wk[$i][4], w1_ot2=$wk[$i][5], 
		w1_vac=$wk[$i][6], w1_sta=$wk[$i][7], w1_sic=$wk[$i][8], w1_brv=$wk[$i][9] WHERE employee_num=$wk[$i][0]";  
   		run_statement ($stmnt);
#		print "$stmnt<br/>";
		$sth->finish;	
	}
	# update data for week 2
	my $stmnt = "SELECT PH.employee_num, SUM(PH.worked), SUM(PH.hours), SUM(PH.reg), SUM(PH.ot1), SUM(PH.ot2), SUM(PH.vac), SUM(PH.sta), 
				SUM(PH.sic), SUM(PH.brv) FROM payroll_hrs as PH JOIN employees AS E ON (E.employee_num = PH.employee_num) 
				WHERE week_num = $wk2 AND E.agency = 'MCK' GROUP BY PH.employee_num";
#	print "$stmnt<br/>";
	run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @wk, [@tmp];
	}
	$sth->finish;
	for my $i (0..$#wk ) {
		$stmnt = "UPDATE payroll_temp SET w2_worked=$wk[$i][1], w2_hours=$wk[$i][2], w2_reg=$wk[$i][3], w1_ot2=$wk[$i][4], w1_ot2=$wk[$i][5], 
		w2_vac=$wk[$i][6], w2_sta=$wk[$i][7], w2_sic=$wk[$i][8], w2_brv=$wk[$i][9] WHERE employee_num=$wk[$i][0]";  
		run_statement ($stmnt);
#		print "$stmnt<br/>";
		$sth->finish;	
	}
	my $stmnt = "SELECT start, end FROM payroll_periods WHERE pay_num = $period";
 	run_statement ($stmnt);
  	my ($start, $end) = $sth->fetchrow;
	$sth->finish;
	my $msg = "Table payroll_temp written for pay period $start thru $end."; 
	return $msg;
}

sub get_agencies
{
	my @agents=();
	# WHERE id <> 'MCK'
   my $stmnt = "SELECT id FROM agency ORDER BY id";
   & run_statement ($stmnt);
   while (($id) = $sth->fetchrow) {
#   	print "FOUND $id<br/>";
      push (@agents, $id);
   }
   $sth->finish;
   return \@agents;
}

sub get_facilities
{
	my @facility=();
	my @tmp=();
   my $stmnt = "SELECT facility_num, facility_code FROM facility WHERE active = 'Y' ORDER BY facility_num";
   run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) {
      push @facility, [@tmp];
   }
   $sth->finish;
   return \@facility;
}

sub clear_agency_hrs_temp
{
      my $stmnt = "DELETE FROM agency_hrs_temp";
      & run_statement ($stmnt);
      $sth->finish;  
}

sub write_agency_hrs_temp
{
	my $start = $_[0];
	my $end = $_[1];
	my $agency = $_[2];
	my $facility = $_[3];
	my @ag=();
	my %tot;
	my $now = get_now();	
#	print "$start, $end, $agency, $facility, $now<br/>";

	my $stmnt = "
					SELECT PH.employee_num, CONCAT(E.last_name, ', ', E.first_name), SUM(PH.hours), SUM(PH.reg), SUM(PH.ot1), SUM(PH.ot2) 
					FROM payroll_hrs AS PH
					JOIN employees AS E ON (E.employee_num = PH.employee_num) 
					WHERE PH.date >= '$start' AND PH.date <= '$end' AND E.facility = $facility AND E.agency='$agency' AND E.employee_num > 10
					GROUP BY PH.employee_num 
					ORDER BY E.last_name
					";	
   & run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) {
		push @ag, [@tmp];
	}
	for my $i (0..$#ag ) {
		$tot{hours}+=$ag[$i][2];
		$tot{reg}+=$ag[$i][3];
		$tot{ot1}+=$ag[$i][4];
		$tot{ot2}+=$ag[$i][5];
		$stmnt = "INSERT INTO agency_hrs_temp (agency, start, end, facility, employee_num, name, hours, reg, ot1, ot2, stamp) 
		VALUES 
		( '$agency', '$start', '$end', $facility, $ag[$i][0], '$ag[$i][1]', $ag[$i][2], $ag[$i][3], $ag[$i][4], $ag[$i][5], '$now' )"; 
   	& run_statement ($stmnt);
#		print "$stmnt<br/>";
		$sth->finish;	
	}
	$stmnt = "INSERT INTO agency_hrs_temp (name, hours, reg, ot1, ot2) VALUES ( 'TOTALS:', $tot{hours}, $tot{reg}, $tot{ot1}, $tot{ot2} )"; 
  	& run_statement ($stmnt);
	# print "$stmnt<br/>";
	$sth->finish;	
}

sub check_new_perm_status
{
	my $num = $_[0];
	my $log = $_[1];
	my $value;
	$stmnt = "SELECT COUNT(*) from employees where employee_num = $num";
#	print "$stmnt<br/>";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$num_count = $rs->[0]->[0];
	$sth->finish;
	$stmnt = "SELECT COUNT(*) from employees where log_id = '$log'";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$log_count = $rs->[0]->[0];
#	print "$stmnt<br/>";
	$sth->finish;
#	print "$num NUM: $num_count $log LOG: $log_count<br>";
	if ($num_count == 0 && $log_count == 0) {
		$value = 0;
	} elsif ($num_count > 0 && $log_count > 0) {
		$value = 3;
	} elsif 	($num_count > 0) {
		$value = 1;
	} else {
		$value = 2;
	}		 			
	return $value;	
}

sub add_new_perm_employee
{
	my $prmFirst = $_[0];	
	my $prmLast = $_[1];
	my $prmLogin = $_[2];
	my $prmLocation = $_[3];
	my $prmFacility = $_[4];
	my $prmPayCat = $_[5];
	my $prmJob =  $_[6];
	my $prmRate = $_[7];
	my $prmNum = $_[8];
	my $now = get_now;
	my $stmnt = "INSERT INTO employees (employee_num, first_name, last_name, location, facility, agency, job, pay_cat, rate, log_id, active, stamp) 
	VALUES ( $prmNum, '$prmFirst', '$prmLast', '$prmLocation', '$prmFacility', 'MCK', '$prmJob', '$prmPayCat', '$prmRate', '$prmLogin', 'Y', '$now')";
#	print "$stmnt<br/>";
   & run_statement ($stmnt);
   $sth->finish;
}

sub check_employee_number
{
	my $num = $_[0];
	my $stmnt = "select COUNT(*) from employees where employee_num = '$num'";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $count = $rs->[0]->[0];
	$sth->finish;
	return $count;
}

sub get_employee_data
{
	my $num = $_[0]; 
   my $stmnt = "select employee_num, first_name, last_name, location, facility, agency, job, pay_cat, rate, log_id, active from employees where employee_num = '$num'";
   & run_statement ($stmnt);
   my @info = $sth->fetchrow;
   $sth->finish;
   return \@info;
}

sub update_employee 
{
	my $mNum = $_[0];
	my $mFir = $_[1];
	my $mLas = $_[2];
	my $mLoc = $_[3];
	my $mFac = $_[4];  		
	my $mAge = $_[5];
	my $mJob = $_[6];
	my $mPay = $_[7];
	my $mRat = $_[8];
	my $mLog = $_[9];
	my $mAct = $_[10];
   my $stmnt = "UPDATE employees SET first_name = '$mFir', last_name = '$mLas', location = '$mLoc', facility='$mFac', agency='$mAge', job='$mJob', pay_cat='$mPay', 
   				rate='$mRat', log_id ='$mLog', active='$mAct' WHERE employee_num = '$mNum'";
   run_statement ($stmnt);
   $sth->finish;
}   
	
sub create_tmp_tc_table
{
	my $supNum = $_[0];
	my $tbl = "tc_tmp_" . $supNum;
   my $stmnt = "DROP TABLE IF EXISTS $tbl";
#	print "$stmnt<br/>";
	run_statement ($stmnt);
#   $stmnt = "DELETE FROM $tbl"; # this was added so that sup @ local host clears the table as DROP is not allowed
#	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$stmnt = "CREATE TABLE $tbl (id MEDIUMINT PRIMARY KEY AUTO_INCREMENT, job_event_id INT(9), tag VARCHAR(12), job_num INT(7),task_num INT(7), date DATE ,
	employee_num INT, start TIME, end TIME, start_sec INT, end_sec INT, doc_num INT(7), client_id VARCHAR (12), doc_ref INT(7), client_ref VARCHAR(12),
	iter TINYINT, source VARCHAR(6)) AUTO_INCREMENT = 10000";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$sth->finish;
}		

sub create_tmp_task_table
{
	my $supNum = $_[0];
	my $supName = $_[1];
	my $supLoc = $_[2];
	my $empNum = $_[3];
	my $empName = $_[4];
	my $payDate = $_[5];
	my $jobNum = $_[6];
	my $tbl = "tmp_task_" . $supNum;
	my $stmnt = "SELECT client_id, doc_num FROM job_headers WHERE job_num = '$jobNum' AND employee_num = '$empNum'";
#	print "$stmnt<br/>";
	run_statement ($stmnt);
  	my ($cliID, $docNum) = $sth->fetchrow;
#	print "FOUND CLIENT! $cliID and $docNum<br/>";
	$stmnt = "DROP TABLE IF EXISTS $tbl";
#	print "$stmnt<br/>";
	run_statement ($stmnt);
	$stmnt = "CREATE TABLE $tbl (id MEDIUMINT PRIMARY KEY AUTO_INCREMENT, date DATE, employee_num INT, job_num INT(9), task_num INT(7), doc_num INT(7), 
			client_id VARCHAR (12), chrg_code VARCHAR(12), description VARCHAR(50), units VARCHAR(10), std_min DECIMAL(8,3), 
			rate DECIMAL(8,3),volume DECIMAL(8,2), total DECIMAL(8.2), adjustment DECIMAL(8.2), source VARCHAR(6) NOT NULL DEFAULT 'ADJ') AUTO_INCREMENT = 10000";
	run_statement ($stmnt);
	# print "$stmnt<br/>";
	$stmnt = "INSERT INTO $tbl (chrg_code, description, units, std_min, rate) 
			SELECT chrg_code, description, units, std_min, rate FROM tasks WHERE client_id = '$cliID'"; 
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$stmnt = "UPDATE $tbl SET date = '$payDate', employee_num = $empNum, job_num = '$jobNum', doc_num = '$docNum', client_id = '$cliID'"; 
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	my $stmnt = "SELECT TE.task_code, SUM(TE.volume) FROM task_events AS TE JOIN job_headers AS JH ON (JH.job_num = TE.job_num) 
				WHERE TE.job_num = $jobNum AND JH.employee_num = $empNum GROUP BY TE.task_code";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	my @tasks = ();
  	while ((@tmp) = $sth->fetchrow) {
		push @tasks, [@tmp];
	}
	for my $i (0..$#tasks ) {
		my $code = $tasks[$i][0];
		my $volume = $tasks[$i][1];
		my $stmnt = "UPDATE $tbl SET total = $volume WHERE chrg_code = '$code'";
		run_statement ($stmnt);
#		print "$stmnt<br/>";
	}
	$sth->finish;
	my $str = $cliID . " : " . $docNum;	
	return $str;
}

sub adjust_tmp_task_table 
{
 my $supNum = $_[0]; 
 my $empNum = $_[1]; 
 my $payDate = $_[2];
 my $jobNum = $_[3];
 my $task = $_[4];
 my $value = $_[5];
 my $taskNum = $_[6];
 my $tbl = "tmp_task_" . $supNum;
 my $stmnt = "UPDATE $tbl SET volume = $value, adjustment = ($value-total), task_num = $taskNum  WHERE chrg_code = '$task'";
 run_statement ($stmnt);
 # print "$stmnt<br/>";
 $sth->finish;
}

sub get_iteration_number
{
	my $jobNum = $_[0];
	my $iter = 0;	

	my $stmnt = "select COUNT(*) from task_events WHERE job_num = $jobNum";
	& run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $taskCount = $rs->[0]->[0];
	if ( $taskCount > 0 ) {
		my $stmnt = "select MAX(iter) from task_events WHERE job_num = $jobNum";
		& run_statement ($stmnt);
		$rs = $sth->fetchall_arrayref();	
		$iter = $rs->[0]->[0];
	}
	$sth->finish;
	$ITER = $iter +1;
	return $ITER;
}


sub write_task_changes 
{
 my $supNum = $_[0]; 
 my $empNum = $_[1]; 
 my $payDate = $_[2];
 my $jobNum = $_[3];
 my $iter = get_iteration_number ($jobNum);
 my $tbl = "tmp_task_" . $supNum;
 my $stmnt = "INSERT INTO task_events (job_num, task_num, task_code, volume, stamp, source, iter, active) 
 			SELECT job_num, task_num, chrg_code, adjustment, date, 'ADJ', $iter, 'Y' FROM $tbl 
 			WHERE adjustment IS NOT NULL AND adjustment <> 0 AND employee_num = $empNum AND date = '$payDate' AND job_num = $jobNum";
 run_statement ($stmnt);
 update_task_std_time ($jobNum);
 my $stmnt = "DROP TABLE IF EXISTS $tbl";
 run_statement ($stmnt);
 $sth->finish;
}

sub get_task_data
{
	my $supNum = $_[0];
	my $empNum = $_[1];
	my $jobNum = $_[2];
	my $payDate = $_[3];
	my @tasks = ();
	my $tbl = "tmp_task_" . $supNum;
	my $stmnt = "SELECT chrg_code, description, units, total FROM $tbl WHERE employee_num = $empNum AND job_num = $jobNum AND date = '$payDate'";
	run_statement ($stmnt);
  	while ((@tmp) = $sth->fetchrow) {
		push @tasks, [@tmp];
	}
	my $stmnt = "UPDATE $tbl SET total = 0 WHERE total IS NULL";
	run_statement ($stmnt);
	$sth->finish;
	return \@tasks;
}


sub get_task_job_list

{
	my $empNum = $_[0];
	my $date = $_[1];
	my @jobList;
	my @tmp;
	my $stmnt = "SELECT JH.job_num, JH.doc_num, JH.client_id, JH.employee_num FROM job_headers AS JH 
					JOIN clients as CL ON (CL.client_id = JH.client_id ) 
					WHERE DATE(JH.stamp) = '$date' AND JH.employee_num = $empNum AND CL.category > 7 AND CL.client_id NOT IN ('BREAK','IDLE')";
   run_statement ($stmnt);
   while ((@tmp) = $sth->fetchrow) {
      push @jobList, [@tmp];
   }
   $sth->finish;
   return \@jobList;
}

sub fill_tmp_tc_clean
{
	my $supNum = $_[0];
	my $empNum = $_[1];
	my $payDate = $_[2];
	my $table = "tc_tmp_" . $supNum;
	my $stmnt = "INSERT INTO $table (id, tag, start, end, start_sec, end_sec, source) SELECT id, tag, start, end, start_sec, 
				end_sec, source FROM card_template WHERE active = 'Y'";
	run_statement ($stmnt);
	$stmnt = "UPDATE $table SET employee_num = $empNum, date = '$payDate'";	
	run_statement ($stmnt);
	$stmnt = "UPDATE $table SET client_id = NULL WHERE start > (SELECT MAX(TIME(JE.end)) FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
			WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum) 
			OR end < (SELECT MIN(TIME(JE.start)) FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
			WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum)";
	run_statement ($stmnt);
	$stmnt = "UPDATE $table SET client_id = NULL, end = (SELECT MIN(TIME(JE.start)) FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
			WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum WHERE start < (SELECT MIN(TIME(JE.start)) 
			FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num) WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum 
			AND end > (SELECT MIN(TIME(JE.start)) FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num)  
			WHERE JE.pay_date = '$payDate'AND JH.employee_num = $empNum)";
	run_statement ($stmnt);
	$stmnt = "UPDATE $table SET client_id = NULL, start = (SELECT MAX(TIME(JE.end)) FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
			WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum) WHERE start < (SELECT MAX(TIME(JE.end)) FROM job_events AS JE 
			JOIN job_headers AS JH ON (JH.job_num = JE.job_num) WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum) 
			AND end > (SELECT MAX(TIME(JE.end)) FROM job_events AS JE JOIN job_headers AS JH ON (JH.job_num = JE.job_num) WHERE JE.pay_date = 'payDate' 
			AND JH.employee_num = $empNum)";
	run_statement ($stmnt);
	$sth->finish;
}

sub fill_tmp_tc_dirty
{
	my $jbsRef = $_[0];
	my $supNum = $_[1];
	my $table = "tc_tmp_" . $supNum;
	my @jbs = @$jbsRef;
	my $stmnt = "";
	for (my $i=0; $i<=$#jbs; $i++) {
		my $empNum =  $jbs[$i][0];
		my $payDate = $jbs[$i][1];
		my $jobNum = $jbs[$i][2];
#		my $taskNum = $jbs[$i][3];
		my $docNum = $jbs[$i][3];
		my $cliID = $jbs[$i][4];
		my $start = $jbs[$i][5];
		my $end = $jbs[$i][6];
		my $diff = $jbs[$i][7];
		my $source = $jbs[$i][8];
		my ($tag, $ampm);
#		if ($cliID ne "" ) {
#			print "OLA $empNum, $payDate, $jobNum, $taskNum, $docNum, $cliID, $start, $end, $diff, $source<br/>";
#		}
		if ($source eq "TEMP" ) {
			$stmnt = "INSERT INTO $table (tag, job_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source) VALUES 
				(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),NULL,'$payDate',$empNum,SEC_TO_TIME($start),SEC_TO_TIME($end),$start,$end,NULL,NULL,NULL,NULL, '$source')";
#			print "1 $stmnt<br/>";	
		} else {
			if ( defined $docNum && $docNum ne "")  {
				$stmnt = "INSERT INTO $table (tag, job_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, 					client_ref, source)
				VALUES 
				(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end),
				$start, $end, $docNum, $docNum, '$cliID', '$cliID', '$source')";
#					print "$stmnt<br/>";	
#				} else {
#					$stmnt = "INSERT INTO $table (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
#					VALUES
#					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, $taskNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end), $start, $end, $docNum, $docNum, NULL, NULL, '$source')";
#				}				
			} else {
#				if ( defined $cliID && $cliID ne "")  {
					$stmnt = "INSERT INTO $table (tag, job_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
					VALUES 
					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, '$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end),
					$start, $end, NULL, NULL, '$cliID', '$cliID', '$source')";
#					print "$stmnt<br/>";	
#				} else {
#					$stmnt = "INSERT INTO $table (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
#					VALUES 
#					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, $taskNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end),
#					$start, $end, NULL, NULL, NULL, NULL, '$source')";
#				}	
			}		
		}
		run_statement ($stmnt);	
		$sth->finish;
#		print "$stmnt<br/>";
	}				 	
}	

sub update_clean_tc_tmp_table_PLACE_HOLDERS
{
	my $updates = $_[0];
	my $supNum = $_[1];
	my $tbl = "tc_tmp_" . $supNum;
	my @updates = @$updates;
	my $stmnt1 = "UPDATE $tbl SET doc_num = ?, client_id = ? WHERE id = ?";
   my $sth = $dbh ->prepare($stmnt1);
	for (my $i=0; $i<=$#updates; $i++) {
		my $id = $updates[$i][0];
		my $doc = $updates[$i][1];
		my $cli = $updates[$i][2];
		if ( $cli eq 'NUL') {
			$sth->execute("NULL", "NULL", $id);			
			print "1 $stmnt<br/>";			
#			$dbh->commit || die $dbh->errstr;
#			$stmnt = "UPDATE $tbl SET doc_num = NULL, client_id = NULL WHERE id = $id";
		} else {
				if ( $doc eq "") {
					$sth->execute(NULL, '$cli', $id);
					print "1 $stmnt<br/>";			
#					$dbh->commit || die $dbh->errstr;
#					$stmnt = "UPDATE $tbl SET doc_num = NULL, client_id = '$cli' WHERE id = $id";	
				} else {
					$sth->execute($doc, '$cli', $id);			
					print "2 $stmnt<br/>";			
#					$dbh->commit || die $dbh->errstr;

#					$stmnt = "UPDATE $tbl SET doc_num = $doc, client_id = '$cli' WHERE id = $id";	
				}
		}	
#		my $stmnt = "UPDATE $tbl SET doc_num = $doc, client_id = '$cli' WHERE id = $id";
#		run_statement ($stmnt);
		$sth->finish;
#		print "$stmnt<br/>";
	}				 	
}



sub 	create_tmp_tc_table_FULL
{
	my $supNum = $_[0];
	my $empNum = $_[1];
	my $payDate = $_[2];
	my $jobCount = $_[3];
	my $tbl = "tc_tmp_" . $supNum;
	# use stored proceedures to create the temporary tables
	if ($jobCount == 0 ) {
		# no jobs for the day open a blank time card
		my $stmnt = "CALL gen_tc_temp ($supNum, $empNum, '$payDate')";
#		print "$stmnt<br/>";
		run_statement ($stmnt);
		$sth->finish;
	} else {
		# if there are jobs for the day open a time card and fill
		my $stmnt = "CALL gen_tc_temp_blank ($supNum, $empNum, '$payDate')";
#		print "$stmnt<br/>";
		run_statement ($stmnt);
		$sth->finish;
	}			
}		

sub update_clean_tc_tmp_table_PLACE_HOLDERS
{
	my $updates = $_[0];
	my $supNum = $_[1];
	my $tbl = "tc_tmp_" . $supNum;
	my @updates = @$updates;
	my $stmnt1 = "UPDATE $tbl SET doc_num = ?, client_id = ? WHERE id = ?";
   my $sth = $dbh ->prepare($stmnt1);
	for (my $i=0; $i<=$#updates; $i++) {
		my $id = $updates[$i][0];
		my $doc = $updates[$i][1];
		my $cli = $updates[$i][2];
		if ( $cli eq 'NUL') {
			$sth->execute("NULL", "NULL", $id);			
			print "1 $stmnt<br/>";			
#			$dbh->commit || die $dbh->errstr;
#			$stmnt = "UPDATE $tbl SET doc_num = NULL, client_id = NULL WHERE id = $id";
		} else {
				if ( $doc eq "") {
					$sth->execute(NULL, '$cli', $id);
					print "1 $stmnt<br/>";			
#					$dbh->commit || die $dbh->errstr;
#					$stmnt = "UPDATE $tbl SET doc_num = NULL, client_id = '$cli' WHERE id = $id";	
				} else {
					$sth->execute($doc, '$cli', $id);			
					print "2 $stmnt<br/>";			
#					$dbh->commit || die $dbh->errstr;

#					$stmnt = "UPDATE $tbl SET doc_num = $doc, client_id = '$cli' WHERE id = $id";	
				}
		}	
#		my $stmnt = "UPDATE $tbl SET doc_num = $doc, client_id = '$cli' WHERE id = $id";
#		run_statement ($stmnt);
		$sth->finish;
#		print "$stmnt<br/>";
	}				 	
}



sub update_clean_tc_tmp_table
{
	my $updates = $_[0];
	my $supNum = $_[1];
	my $tbl = "tc_tmp_" . $supNum;
	my @updates = @$updates;
 	my $stmnt = "";
	for (my $i=0; $i<=$#updates; $i++) {
		my $id = $updates[$i][0];
		my $doc = $updates[$i][1];
		my $cli = $updates[$i][2];
		if ( $cli eq 'NUL') {
				$stmnt = "UPDATE $tbl SET doc_num = NULL, client_id = NULL, source='TC' WHERE id = $id";
		} else {
				if ( $doc eq "") {
					$stmnt = "UPDATE $tbl SET doc_num = NULL, client_id = '$cli', source='TC' WHERE id = $id";	
				} else {
					$stmnt = "UPDATE $tbl SET doc_num = $doc, client_id = '$cli', source='TC' WHERE id = $id";	
				}
		}	
#		my $stmnt = "UPDATE $tbl SET doc_num = $doc, client_id = '$cli' WHERE id = $id";
		run_statement ($stmnt);
		$sth->finish;
#		print "$stmnt<br/>";
	}				 	
}

sub fill_dirty_tc_tmp_table
{
	my $jbsRef = $_[0];
	my $supNum = $_[1];
	my $table = "tc_tmp_" . $supNum;
	my @jbs = @$jbsRef;
	my $stmnt = "";
	for (my $i=0; $i<=$#jbs; $i++) {
		my $empNum =  $jbs[$i][0];
		my $payDate = $jbs[$i][1];
		my $jobNum = $jbs[$i][2];
		my $taskNum = $jbs[$i][3];
		my $docNum = $jbs[$i][4];
		my $cliID = $jbs[$i][5];
		my $start = $jbs[$i][6];
		my $end = $jbs[$i][7];
		my $diff = $jbs[$i][8];
		my $source = $jbs[$i][9];
		my ($tag, $ampm);
		
		if ($source eq "TEMP" ) {
#			print "$source<br/>";
			$stmnt = "INSERT INTO $table (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source) VALUES 
				(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),NULL, NULL,'$payDate',$empNum,SEC_TO_TIME($start),SEC_TO_TIME($end),$start,$end,NULL,NULL,NULL,NULL, '$source')";
		} else {
			if ( defined $docNum && $docNum ne "")  {
				if ( defined $cliID && $cliID ne "")  {
					$stmnt = "INSERT INTO $tbl (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
					VALUES 
					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, $taskNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end),
					$start, $end, $docNum, $docNum, '$cliID', '$cliID', '$source')";
				} else {
					$stmnt = "INSERT INTO $tbl (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
					VALUES
					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, $taskNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end), $start, $end, $docNum, $docNum, NULL, NULL, '$source')";
				}				
			} else {
				if ( defined $cliID && $cliID ne "")  {
					$stmnt = "INSERT INTO $tbl (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
					VALUES 
					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, $taskNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end),
					$start, $end, NULL, NULL, '$cliID', '$cliID', '$source')";
				} else {
					$stmnt = "INSERT INTO $tbl (tag, job_num, task_num, date, employee_num, start, end, start_sec, end_sec, doc_num, doc_ref, client_id, client_ref, source)
					VALUES 
					(TIME_FORMAT(SEC_TO_TIME($start),'%h:%i %p'),$jobNum, $taskNum,'$payDate',$empNum, SEC_TO_TIME($start), SEC_TO_TIME($end),
					$start, $end, NULL, NULL, NULL, NULL, '$source')";
				}	
			}		
		}
		run_statement ($stmnt);	
		$sth->finish;
#		print "$stmnt<br/>";
	}				 	
}




sub check_tc_tmp_data
{
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
 	my $table = "tc_tmp_" . $supNum;
	my $stmnt = "SELECT COUNT(*) FROM $table WHERE client_id IS NOT NULL AND employee_num = $empNum AND date = '$payDate'";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$rs = $sth->fetchall_arrayref();
	my $data_check = $rs->[0]->[0];
	$sth->finish;
	return $data_check;
}

sub generate_clean_time_card_events
{
	# This should is straight forward, create a day event, job headers and job_events 
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
 	my $table = "tc_tmp_" . $supNum;
	my $i;
	my @tmp=();
	my @jobs=();
	# get an array of all of the work in the temp table - client can not be null!
	my $stmnt = "SELECT id, employee_num, date, start, end, doc_num, client_id, source FROM $table WHERE client_id IS NOT NULL ORDER BY id";
	run_statement ($stmnt);
#	print "$stmnt<br/>"; 
	while ((@tmp) = $sth->fetchrow) {
		push @jobs, [@tmp];
	}
 	# create a day event for the employee getting start and end times
	my $first = "$jobs[0][2]" . " " . "$jobs[0][3]";
	my $last = "$jobs[-1][2]" . " " . "$jobs[-1][4]";
	$stmnt = "INSERT INTO day_events (employee_num, start, end, elapsed, source) VALUES ($empNum, '$first', '$last', TIMEDIFF('$last','$first'), 'TC')";  
	run_statement ($stmnt);
 	print "$stmnt<br/>";
#	die "KILLED BY ALAN";
	my	$id = $jobs[0][0];
	my $employee_num = $jobs[0][1];
	my $date = $jobs[0][2];
	my $start = $jobs[0][3];
	my $end = $jobs[0][4];
	my $Start = "$date" . " ". "$start";
	my $End = "$date" . " ". "$end";
	my $doc_num = $jobs[0][5];
	if ($doc_num eq "") {
		$doc_num = "NULL";
	}
	my $client_id = $jobs[0][6];
	my $source = $jobs[0][7];
	
#	print "START: $Start END: $End<br/>";
	my $job_num = get_new_count(JOB);
#	my $task_num = get_new_count(TASK);
	# generate a new job header for the first job in the array
	$stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp, source) VALUES ($job_num, $doc_num, '$client_id', $empNum, '$Start', '$source')";  
 	run_statement ($stmnt);
	print "$stmnt<br/>";
	# generate a new job_event for the first job in the array
	$stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj, source) VALUES ($job_num, '$date','$Start', '$End', 
				TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60,  TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, '$source')";  
 	run_statement ($stmnt);
 #	print "$stmnt<br/>";
	for ($i=1;$i<=$#jobs;$i++){
		$id = $jobs[$i][0];
		$employee_num = $jobs[$i][1];
		$date = $jobs[$i][2];
		$start = $jobs[$i][3];
		$end = $jobs[$i][4];
		$Start = "$date" . " ". "$start";
		$End = "$date" . " ". "$end";
		$doc_num = $jobs[$i][5];
		if ($doc_num eq "") {
			$doc_num = "NULL";
	   }	
		$client_id = $jobs[$i][6];
#		print "$id, $employee_num, $date, $start, $end, $doc_num, $client_id<br/>";
		if ($jobs[$i-1][6] == $jobs[$i][6] && $jobs[$i-1][5] == $jobs[$i][5] && $jobs[$i][5] ne "")  {
			# if client id and job num (can't be null) do not change update the previous job event or else ...
			$stmnt = "UPDATE job_events SET end = '$End', elapsed = TIME_TO_SEC(TIMEDIFF('$End', start))/60, elapsed_adj = TIME_TO_SEC(TIMEDIFF('$End', start))/60   WHERE id = LAST_INSERT_ID()";
			run_statement ($stmnt);
#			print "$stmnt<br/>";
		} else {
			# create a new job header and  job event
			$job_num = get_new_count(JOB);
#			$task_num = get_new_count(TASK);
			$stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp, source) VALUES ($job_num, $doc_num, '$client_id', $empNum, '$Start', 'TC')";  
		 	run_statement ($stmnt);
#			print "OLA $stmnt<br/>";
			$stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj, source) VALUES ($job_num, '$date','$Start', '$End', 
				TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60,  TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, 'TC')"; 
 			run_statement ($stmnt);
			$sth->finish;
#			print "OLA2 $stmnt<br/>";
		}	
	}
	update_daily_time_stats ($empNum, $payDate);
}


sub generate_dirty_time_card_events
{
	# This is now straight forward(er) all job_headers and job_events will be recreated and the day_event changed
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
 	my $table = "tc_tmp_" . $supNum;
	my $i;
	my @tmp=();
	my @jobs=();
	# get an array of all of the work in the temp table - client can not be null!
	my $stmnt = "SELECT id, employee_num, date, start, end, doc_num, client_id FROM $table WHERE client_id IS NOT NULL ORDER BY id";
	run_statement ($stmnt);
#	print "$stmnt<br/>"; 
	while ((@tmp) = $sth->fetchrow) {
		push @jobs, [@tmp];
	}
   $sth->finish;
	my	$id = $jobs[0][0];
	my $employee_num = $jobs[0][1];
	my $date = $jobs[0][2];
	my $start = $jobs[0][3];
	my $end = $jobs[0][4];
	my $Start = "$date" . " ". "$start";
	my $End = "$date" . " ". "$end";
	my $doc_num = $jobs[0][5];
	if ($doc_num eq "") {
		$doc_num = "NULL";
	}
	my $client_id = $jobs[0][6];
#	print "START: $Start END: $End<br/>";
	my $job_num = get_new_count(JOB);
#	my $task_num = get_new_count(TASK);
	# generate a new job header for the first job in the array
	$stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp, source) VALUES ($job_num, $doc_num, '$client_id', $empNum, '$Start', 'TC')";  
 	run_statement ($stmnt);
	$sth->finish;
#	print "$stmnt<br/>";
	# generate a new job_event for the first job in the array
	$stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj, source) VALUES ($job_num, '$date','$Start', '$End', 
				TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60,  TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, 'TC')";  
 	run_statement ($stmnt);
	$sth->finish;
# 	print "$stmnt<br/>";
	for ($i=1;$i<=$#jobs;$i++){
		$id = $jobs[$i][0];
		$employee_num = $jobs[$i][1];
		$date = $jobs[$i][2];
		$start = $jobs[$i][3];
		$end = $jobs[$i][4];
		$Start = "$date" . " ". "$start";
		$End = "$date" . " ". "$end";
		$doc_num = $jobs[$i][5];
		if ($doc_num eq "") {
			$doc_num = "NULL";
	   }	
		$client_id = $jobs[$i][6];
#		print "$id, $employee_num, $date, $start, $end, $doc_num, $client_id<br/>";
		if ($jobs[$i-1][6] == $jobs[$i][6] && $jobs[$i-1][5] == $jobs[$i][5] && $jobs[$i][5] ne "")  {
			# if client id and job num (can't =be null) do not change update the previous job event or else ...
			$stmnt = "UPDATE job_events SET end = '$End', elapsed = TIME_TO_SEC(TIMEDIFF('$End', start))/60, elapsed_adj = TIME_TO_SEC(TIMEDIFF('$End', start))/60   WHERE id = 							 LAST_INSERT_ID()";
			run_statement ($stmnt);
#			print "$stmnt<br/>";
			$sth->finish;
		} else {
			# create a new job header and  job event
			$job_num = get_new_count(JOB);
#			$task_num = get_new_count(TASK);
			$stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp, source) VALUES ($job_num, $doc_num, '$client_id', $empNum, '$Start', 'TC')";  
		 	run_statement ($stmnt);
#			print "OLA $stmnt<br/>";
			$sth->finish;
			$stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj, source) VALUES ($job_num, '$date','$Start', '$End', 
				TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60,  TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, 'TC')"; 
 			run_statement ($stmnt);
			$sth->finish;
#			print "OLA2 $stmnt<br/>";
		}	
	}
	# update the existing day event for the employee getting start and end times
	update_day_event ($empNum, $payDate);
	update_daily_time_stats ($empNum, $payDate);
}

sub count_job_events
{
 	my $empNum = $_[0];
 	my $payDate = $_[1];
	my $stmnt = "SELECT COUNT(*) FROM job_events AS JE JOIN job_headers as JH ON (JH.job_num = JE.job_num) 
	WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $job_count = $rs->[0]->[0];
	$sth->finish;
#	print "$stmnt<br/>";
#	print "JOB COUNT : $job_count WHAT<br/>";
	return $job_count;
}

sub get_punch_in_out
{
	my @times=();
 	my $empNum = $_[0];
 	my $payDate = $_[1];
 	my $stmnt = "SELECT MIN(TIME_TO_SEC(TIME(JE.start))) AS Start, MAX(TIME_TO_SEC(TIME(JE.end))) AS End FROM job_events AS JE 
 					JOIN job_headers as JH ON (JH.job_num = JE.job_num) WHERE pay_date = '$payDate' AND JH.employee_num = $empNum ";
 	run_statement ($stmnt);
	@times = $sth->fetchrow;
	$sth->finish;
	return \@times;
}

sub get_job_events
{
	my @tmp=();
	my @jobs=();
 	my $empNum = $_[0];
 	my $payDate = $_[1];
	my $stmnt = "SELECT JH.employee_num, JE.pay_date, JE.job_num, JH.doc_num, JH.client_id, 
					TIME_TO_SEC(TIME(JE.start)) AS Start, TIME_TO_SEC(TIME(JE.end)) AS End, JE.source
					FROM job_events AS JE JOIN job_headers as JH ON (JH.job_num = JE.job_num)  
					WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum AND JE.active = 'Y' AND JE.elapsed > $minTime";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	while ((@tmp) = $sth->fetchrow) {
		push @jobs, [@tmp];
	}
	$sth->finish;
	return \@jobs;
}

sub get_time_card_start_end_sec
{
	my $stmnt = "SELECT MIN(start_sec), MAX(end_sec) FROM card_template WHERE active = 'Y'";
	run_statement ($stmnt);
	my @tmp = $sth->fetchrow;
	$sth->finish;
	return \@tmp;
}


sub get_time_card_data

{
	my $supNum = $_[0];
	my $tableName = "tc_tmp_" . $supNum;
	my @tmp = ();
	my @t_card = ();
	my $stmnt = "SELECT id, tag, date, employee_num, start, end, doc_num, client_id, source FROM $tableName";  
	run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @t_card, [@tmp];
	}
	$sth->finish;
	return \@t_card;
}

sub get_time_card_clients
{
	my $fac = $_[0];
	my @clients = ();
	my $stmnt;
	if ( $fac == 99 ) {
   	$stmnt = "SELECT client_id, client_name FROM clients WHERE active = 'Y' ORDER BY client_id";
	} else {
	   $stmnt = "SELECT client_id, client_name FROM clients WHERE active = 'Y' AND (facility = $fac OR facility = 100) ORDER BY client_id";
	}
   run_statement ($stmnt);
   while (($client_id, $client_name) = $sth->fetchrow) {
      push (@clients, $client_id);
   }
   $sth->finish;
   return \@clients;
}


sub get_client_info
{
	my $cliId = $_[0];
	my $stmnt = "SELECT client_id, facility, client_name, category, active FROM clients WHERE client_id = '$cliId'";  
	& run_statement ($stmnt);
 	my @info = $sth->fetchrow;
   $sth->finish;
   return \@info;
}

sub change_client_info
{
	my $cliId = $_[0];
	my $facility = $_[1];
	my $client_name = $_[2];
	my $category = $_[3];
	my $active = $_[4];
	my $stmnt = "UPDATE clients SET facility = $facility, client_name = '$client_name', category = $category, active = '$active' WHERE client_id = '$cliId'";  
	run_statement ($stmnt);
#	print "$stmnt <br/>";
   $sth->finish;	
}

sub make_client_active
{
 	my $cliId = $_[0];
	my $stmnt = "UPDATE clients SET  active = 'Y' WHERE client_id = '$cliId' AND active = 'N'";  
	run_statement ($stmnt);
   $sth->finish;	
}


sub create_job_headers_history
{
	#### WIP
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
	my $stmnt = "INSERT INTO job_headers_hist (id, job_num, doc_num, client_id, employee_num, stamp, act_in, std_min, source) 
					SELECT JH.id, JH.job_num, JH.doc_num, JH.client_id, JH.employee_num, JH.stamp, JH.act_in, JH.std_min, JH.source
					FROM job_headers AS JH
					WHERE JH.employee_num = $empNum AND JE.pay_date = '$payDate' AND JE.elapsed > $minTime";
	run_statement ($stmnt);
	print "$stmnt <br/>";
	my $stmnt = "UPDATE job_events AS JE JOIN job_events_hist AS JH ON (JH.id = JE.id) SET JE.job_num = 0, JE.task_num = 0, JE.pay_date = NULL,
					 JE.start = NULL, JE.end = NULL, JE.elapsed = NULL, JE.elapsed_adj = NULL, JE.source = 'HST'  	
					 WHERE JH.pay_date = '$payDate'";
	run_statement ($stmnt);
	print "$stmnt <br/>";
   $sth->finish;   

}



sub create_job_events_history
{
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
	my $stmnt = "INSERT INTO job_events_hist (id, job_num, task_num, pay_date, start, end, elapsed, elapsed_adj, source ) 
					SELECT JE.id, JE.job_num, JE.task_num, JE.pay_date, JE.start, JE.end, JE.elapsed, JE.elapsed_adj, JE.source 
					FROM job_events AS JE 
					JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
					WHERE JH.employee_num = $empNum AND JE.pay_date = '$payDate' AND JE.elapsed > $minTime";
	run_statement ($stmnt);
	print "$stmnt <br/>";
	my $stmnt = "UPDATE job_events AS JE JOIN job_events_hist AS JH ON (JH.id = JE.id) SET JE.job_num = 0, JE.task_num = 0, JE.pay_date = NULL,
					 JE.start = NULL, JE.end = NULL, JE.elapsed = NULL, JE.elapsed_adj = NULL, JE.source = 'HST'  	
					 WHERE JH.pay_date = '$payDate'";
	run_statement ($stmnt);
	print "$stmnt <br/>";
   $sth->finish;   
}


sub get_rate
{
	my $num = $_[0];
	my $stmnt = "SELECT rate FROM employees WHERE employee_num = $num";
    run_statement ($stmnt);
#	print "$stmnt<br/>";
	$rs = $sth->fetchall_arrayref();
	my $rate = $rs->[0]->[0];
	$sth->finish;
#	print "OLA THE RATE IS $rate<br/>";
	return $rate;	
}

sub create_new_job
{
  	my $num = $_[0];
  	my $doc = $_[1];
  	my $cli = $_[2];
  	my $session = $_[3];
	my $dir = $_[4];	
	my $loc = $_[5];
	if ( $dir eq "" ) {
		 $dir ="NA";
	}
#	print "OLA CREATING NEW JOB NUM:$num DOC:$doc CLI:$cli SESSION:$session DIRECTION:$dir<br/>";
	my $rate = get_rate ($num);
	my $count_hold = 0;
	my $count_open = 0;
	my ($stmnt, $stmnt1, $stmnt2);
	my $now = get_local_time($loc);
#	print "OLA $loc NOW IS $now<br/>";
 	my $job_num;	
	# Check if the currently open job matches this document, client and employee
	my ($stmnt, $stmnt1, $stmnt2);
	if ( $doc ne "" ) {
		$stmnt = "SELECT COUNT(*) FROM job_open WHERE doc_num = $doc AND client_id = '$cli' AND employee_num = $num AND dir = '$dir'";
	} else {
	 	$stmnt = "SELECT COUNT(*) FROM job_open WHERE doc_num IS NULL AND client_id = '$cli' AND employee_num = $num AND dir = '$dir'";
	}
   run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$count_open = $rs->[0]->[0];
	$sth->finish;
	# Check if a job exists on hold for this document, client and employee
	if ( $doc ne "" ) {
		$stmnt = "SELECT COUNT(*) FROM job_hold WHERE doc_num = $doc AND client_id = '$cli' AND employee_num = $num";
	} else {
	 	$stmnt = "SELECT COUNT(*) FROM job_hold WHERE doc_num IS NULL AND client_id = '$cli' AND employee_num = $num";
	}
	run_statement ($stmnt);
#	print "$stmnt<br/>"; 
	$rs = $sth->fetchall_arrayref();
	$count_hold = $rs->[0]->[0];
	$sth->finish;

#	print "COUNT_OPEN:$count_open COUNT_HOLD:$count_hold<br/>";

	if ($count_hold == 0 && $count_open == 0) {
		# create a new job
		pause_current_job ($num, $now, $dir);
		$job_num = get_new_count('JOB');
		if ( $doc == "" ) {
			$stmnt1 = "INSERT INTO job_headers (session,job_num, doc_num , client_id, employee_num, stamp, rate) VALUES ( $session, $job_num, NULL, '$cli', $num, '$now', $rate)";
			$stmnt2 = "INSERT INTO job_open (session, doc_num , job_num, employee_num, client_id, stamp, dir) VALUES ( $session,  NULL, $job_num, $num, '$cli', '$now', '$dir')";
		} else {
			$stmnt1 = "INSERT INTO job_headers (session, job_num, doc_num , client_id, employee_num, stamp, rate) VALUES ( $session, $job_num, $doc, '$cli', $num, '$now', $rate)";
			$stmnt2 = "INSERT INTO job_open (session, doc_num , job_num, employee_num, client_id, stamp, dir) VALUES ( $session, $doc, $job_num, $num, '$cli', '$now', '$dir')";
   		}
   		run_statement ($stmnt1);
   		run_statement ($stmnt2);
#		print "$stmnt1<br/>";
#		print "$stmnt2<br/>";
 		$sth->finish;
   } elsif ( $count_open == 1 ) {
   	# do nothing this job is already open!
   } elsif ($count_hold == 1 ) {
		my $hold_id = get_hold_job_id ($num, $doc, $cli);
		pause_current_job ($num, $now, $dir);
		continue_hold_job ($hold_id, $now);
   } else {
   		print "WOAH! There is a problem found $count jobs on hold that match! Call Alan!<br/>";	
   }	
}



sub get_open_job_id
{
   my $num = "$_[0]";
   my $doc = "$_[1]";
   my $cli = "$_[2]";
   my $stmnt;
	if ( $doc ne "" ) {
		$stmnt = "SELECT id FROM job_open WHERE doc_num = $doc AND client_id = '$cli' AND employee_num = $num";
	} else {
	 	$stmnt = "SELECT id FROM job_open WHERE doc_num IS NULL AND client_id = '$cli' AND employee_num = $num";
	}   
   & run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$id = $rs->[0]->[0];
	$sth->finish;
	return $id;
}

sub get_open_job_client
{
	my $num = $_[0];
	my $stmnt = "SELECT client_id FROM job_open WHERE employee_num = $num";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$cli = $rs->[0]->[0];
	$sth->finish;
	return $cli;
}

sub get_hold_job_id
{
   my $num = "$_[0]";
   my $doc = "$_[1]";
   my $cli = "$_[2]";
   my $stmnt;
	if ( $doc ne "" ) {
		$stmnt = "SELECT id FROM job_hold WHERE doc_num = $doc AND client_id = '$cli' AND employee_num = $num";
	} else {
	 	$stmnt = "SELECT id FROM job_hold WHERE doc_num IS NULL AND client_id = '$cli' AND employee_num = $num";
	}   
   run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$id = $rs->[0]->[0];
	$sth->finish;
	return $id;
}

sub get_hold_client_count
{
	my $num = $_[0];
	my $cli = $_[1];
	my $stmnt = "SELECT COUNT(*) FROM job_hold WHERE client_id = '$cli' AND employee_num = $num";
   run_statement ($stmnt);
	my $count = $sth->fetchrow;
	$sth->finish;
	return $count;
}

sub get_hold_job_list
{
	my $num = $_[0];
	my $cli = $_[1];
	my (@tmp, @holds);
	my $stmnt = "SELECT job_num, doc_num, client_id FROM job_hold WHERE employee_num = $num AND client_id = '$cli'";
#	print "$stmnt <br/>";
   run_statement ($stmnt);
	$sth->execute;
	while ((@tmp) = $sth->fetchrow) {
		push @holds, [@tmp];
	}
	$sth->finish;
	return \@holds;
}

sub update_day_event
{
   my $empNum = "$_[0]";
   my $payDate = "$_[1]";
}

sub get_employee_list
{
	my $loc = $_[0];
	my @tmp=();
	my $stmnt = "select employee_num, CONCAT(last_name, ', ', first_name) FROM employees 
				WHERE location = '$loc' AND employee_num > 10 AND job IN ('WHS', 'CSR') AND active = 'Y' ORDER BY last_name";
   run_statement ($stmnt);
   # print "$stmnt<br/>";
	while ((@tmp) = $sth->fetchrow) {
		push @empList, [@tmp];
	}
	$stmnt = "select employee_num, CONCAT(last_name, ', ', first_name) FROM employees 
				WHERE location = '$loc' AND employee_num > 10 AND job = 'TMP' AND active = 'Y' ORDER BY last_name";
   run_statement ($stmnt);
   # print "$stmnt<br/>";
	while ((@tmp) = $sth->fetchrow) {
		push @empList, [@tmp];
	}
	return \@empList;
}

sub get_full_employee_list
{
	my @tmp=();
	my @empList=();
	my $stmnt = "select employee_num, CONCAT(last_name, ', ', first_name) FROM employees 
				WHERE employee_num > 10 AND job IN ('WHS', 'CSR') AND active = 'Y' ORDER BY last_name";
   run_statement ($stmnt);
	while ((@tmp) = $sth->fetchrow) {
		push @empList, [@tmp];
	}
	$stmnt = "select employee_num, CONCAT(last_name, ', ', first_name) FROM employees 
				WHERE employee_num > 10 AND job = 'TMP' AND active = 'Y' ORDER BY last_name";
   run_statement ($stmnt);
   # print "$stmnt<br/>";
	while ((@tmp) = $sth->fetchrow) {
		push @empList, [@tmp];
	}
	return \@empList;
}





sub update_iteration
{
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
	my $table = "tc_tmp_" . $supNum;
	my $stmnt = "SELECT MAX(JE.iter) FROM job_events AS JE JOIN job_headers as JH ON (JH.job_num = JE.job_num)
			WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum AND JE.active = 'Y'";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $iter = $rs->[0]->[0];
	$iter+=1;
	my $stmnt = "UPDATE $table SET iter = $iter";
	run_statement ($stmnt);
	$sth->finish;
	return $iter;
}

sub deactivate_job_events 
{
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
	my $table = "tc_tmp_" . $supNum;
	my $stmnt = "UPDATE job_events AS JE JOIN job_headers as JH ON (JH.job_num = JE.job_num)
		     SET active = 'N' WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum AND JE.elapsed > $minTime"; 
	run_statement ($stmnt);
	$sth->finish;
}

sub write_new_dirty_job_events
{
	# This is straight forward, create a day event, job headers and job_events 
	my $supNum = $_[0];
 	my $empNum = $_[1];
 	my $payDate = $_[2];
 	my $table = "tc_tmp_" . $supNum;
	my $i;
	my @jobs = ();
	# get an array of all of the work in the temp table - client can not be null!
	my $stmnt = "SELECT id, job_event_id, job_num, task_num, date, employee_num, start, end, doc_num, client_id, doc_ref, client_ref, iter, source 
			FROM $table WHERE client_id IS NOT NULL ORDER BY id";
	run_statement ($stmnt);
#	print "$stmnt<br/>"; 
	while ((@tmp) = $sth->fetchrow) {
		push @jobs, [@tmp];
	}
	$id = $jobs[0][0];
	$job_event_id = $jobs[0][1];
	$job_num = $jobs[0][2];
	$task_num = $jobs[0][3];
	$date = $jobs[0][4];
	$employee_num = $jobs[0][5];
	$start = $jobs[0][6];
	$end = $jobs[0][7];
	$doc_num = $jobs[0][8];
	$client_id = $jobs[0][9];
	$doc_ref = $jobs[0][10];
	$client_ref = $jobs[0][11];
	$iter = $jobs[0][12];
	$source = $jobs[0][13];
	$Start = "$date" . " ". "$start";
	$End = "$date" . " ". "$end";
	if ($doc_num eq "") { 
		$doc_num = "NULL";
	}
	if ($doc_ref eq "") { 
		$doc_ref = "NULL";
	}
	# write job event
	if ( $doc_num <=> $doc_ref && $client_id <=> $client_ref ) {
		# No new job header required just write the job_event
		$stmnt = "INSERT INTO job_events (job_num, task_num, pay_date, start, end, elapsed, elapsed_adj, iter, source) VALUES ($job_num, $task_num, '$date','$Start', '$End', 
			TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60,  TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, $iter, '$source')"; 
		run_statement ($stmnt);
#		print "001 $stmnt<br/>";	
	} else {
		# new job header required and new job_num task_num
		$job_num = get_new_count(JOB);
#		$task_num = get_new_count(TASK);
		$stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp, iter,  source) 
				VALUES ($job_num, $doc_num, '$client_id', $empNum, '$Start', $iter, '$source')";  
	 	run_statement ($stmnt);
#		print "002A $stmnt<br/>";	
		$stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj, iter, source) VALUES ($job_num, '$date','$Start', '$End', 
			TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, $iter, '$source')"; 
		run_statement ($stmnt);
#		print "002B $stmnt<br/>";	
	}
	# OK 1st entry taken care of lets march on!
	for ($i=1;$i<=$#jobs;$i++){
		$id = $jobs[$i][0];
		$job_event_id = $jobs[$i][1];
		$job_num = $jobs[$i][2];
		$task_num = $jobs[$i][3];
		$date = $jobs[$i][4];
		$employee_num = $jobs[$i][5];
		$start = $jobs[$i][6];
		$end = $jobs[$i][7];
		$doc_num = $jobs[$i][8];
		$client_id = $jobs[$i][9];
		$doc_ref = $jobs[$i][10];
		$client_ref = $jobs[$i][11];
		$iter = $jobs[$i][12];
		$source = $jobs[$i][13];
		$Start = "$date" . " ". "$start";
		$End = "$date" . " ". "$end";
		if ($doc_num eq "") { 
			$doc_num = "NULL";
		}
		if ($doc_ref eq "") { 
			$doc_ref = "NULL";
		}
#		print "$id, $employee_num, $date, $start, $end, $doc_num, $client_id<br/>";
		if ($jobs[$i-1][9] == $jobs[$i][9] && $jobs[$i-1][8] == $jobs[$i][8] && $jobs[$i][8] ne "")  {
			# if client id and job num (can't be null) do not change update the previous job event no header required ...
			$stmnt = "UPDATE job_events SET end = '$End', elapsed = TIME_TO_SEC(TIMEDIFF('$End', start))/60, 
				elapsed_adj=TIME_TO_SEC(TIMEDIFF('$End', start))/60 WHERE id = LAST_INSERT_ID()";
			run_statement ($stmnt);
#			print "003 $stmnt<br/>";
			$sth->finish;
		} else {
			# create a new job header and  job event
			$job_num = get_new_count(JOB);
#			$task_num = get_new_count(TASK);
			$stmnt = "INSERT INTO job_headers (job_num, doc_num, client_id, employee_num, stamp, iter, source) 
				VALUES ($job_num, $doc_num, '$client_id', $empNum, '$Start', $iter, '$source')";  
		 	run_statement ($stmnt);
#			print "004A $stmnt<br/>";
			$sth->finish;
			$stmnt = "INSERT INTO job_events (job_num, pay_date, start, end, elapsed, elapsed_adj,iter, source) 
				VALUES ($job_num, '$date','$Start', '$End', TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60,
				TIME_TO_SEC(TIMEDIFF('$End', '$Start'))/60, $iter, '$source')"; 
 			run_statement ($stmnt);
			$sth->finish;
#			print "004B $stmnt<br/>";
		}	
	}
#	update_day_event ($empNum, $payDate);
	update_daily_time_stats ($empNum, $payDate);
}

sub clear_day_events 
{
	my $empNum = $_[0];
	my $payDate = $_[1];
	my $stmnt = "DELETE FROM day_events WHERE employee_num = $empNum AND DATE(start) = '$payDate'";
	run_statement ($stmnt);
	$sth->finish;
}

sub	create_day_event  
{
	my $empNum = $_[0];
	my $payDate = $_[1];
	my $stmnt = "SELECT MIN(JE.start), MAX(JE.end) FROM job_events AS JE JOIN job_headers as JH ON (JH.job_num = JE.job_num) 
				WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum AND JE.active = 'Y'";
	run_statement ($stmnt);
	my ($first, $last) = $sth->fetchrow;
	$sth->finish;
	$stmnt = "INSERT INTO day_events (employee_num, start, end, elapsed, source) 
		VALUES ($empNum, '$first', '$last', TIMEDIFF('$last','$first'), 'TC')";
	run_statement ($stmnt);
	$sth->finish;
}

sub clear_useless_iterations
{
	my $empNum = $_[0];
	my $payDate = $_[1];
	my $it =  $_[2];
	my $stmnt = "DELETE JE FROM job_events AS JE JOIN job_headers as JH ON (JH.job_num = JE.job_num) 
				WHERE JE.pay_date = '$payDate' AND JH.employee_num = $empNum AND JE.iter > 1 AND JE.iter < $it";
	run_statement ($stmnt);
	$sth->finish;
#	print "$stmnt<br/>";
	my $stmnt = "DELETE FROM job_headers WHERE DATE(stamp) = '$payDate' AND employee_num = $empNum 
			AND iter > 1 AND iter < $it";
	run_statement ($stmnt);
	$sth->finish;
#	print "$stmnt<br/>";

}

sub get_employee_rate
{
	my $empNum = $_[0];
	my $stmnt = "SELECT rate FROM employees WHERE employee_num = $empNum";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $rate = $rs->[0]->[0];
	$sth->finish;
	return $rate;
}


sub get_match_count_temp 

{
	my $id=$_[0];
	my $cli=$_[1];
	my $task=$_[2];
	my $time=$_[3];
	my $price=$_[4];
	my $stmnt = "SELECT COUNT(*) FROM tasks WHERE client_id = '$cli' AND chrg_code = '$task' AND std_min > 0 AND rate > 0 ";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	$count = $rs->[0]->[0];
	$sth->finish;
	return $count;
}

sub backup_dbase 
{
	my $stmnt = "SELECT DATE_FORMAT(CURDATE(), '%Y_%b_%d_TT.sql')";
	run_statement ($stmnt);
	$rs = $sth->fetchall_arrayref();
	my $name = $rs->[0]->[0];
	$sth->finish;
	system ("mysqldump -p$user_password -u $user_name $db_name > ${back_loc}/${name}");
	return $name;
}

sub fill_rpt_daily_data
{
	my $date = $_[0];
	my $facility = $_[1];
	my $stmnt = "DELETE FROM rpt_daily_employee";
	run_statement ($stmnt);
	my $stmnt = "DELETE FROM rpt_daily_client";
	run_statement ($stmnt);
	$stmnt = "INSERT INTO rpt_daily_employee (date, facility, job, employee_num, name)
			SELECT '$date', EM.facility, EM.job, EM.employee_num, CONCAT(EM.first_name, ' ', EM.last_name) AS name  
			FROM payroll_hrs AS PH 
			JOIN employees AS EM ON (EM.employee_num = PH.employee_num) 
			WHERE PH.date = '$date' AND PH.hours > 0.0 ORDER BY EM.facility, PH.employee_num";
	run_statement ($stmnt);
#	print "$stmnt<br/><br/>";
	$stmnt = "SELECT employee_num FROM rpt_daily_employee";
	run_statement ($stmnt);
  	while (($tmp) = $sth->fetchrow) {
		push @empNums, $tmp;
#		print "EMPLOYEE NUM: $tmp<br/>";
	}
	foreach $num (@empNums) {
		$stmnt = "UPDATE rpt_daily_employee SET on_site = 
				(SELECT (SUM(JE.elapsed))/60 FROM job_events AS JE
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				JOIN clients AS CL ON (CL.client_id = JH.client_id)
				WHERE JE.pay_date = '$date' AND JH.employee_num = $num AND CL.category > 0 AND JE.active = 'Y')
				WHERE employee_num = $num";   						
		run_statement ($stmnt);
#	print "$stmnt<br/><br/>";

		$stmnt = "UPDATE rpt_daily_employee SET client = 
				(SELECT (SUM(JE.elapsed))/60 FROM job_events AS JE
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				JOIN clients AS CL ON (CL.client_id = JH.client_id)
				WHERE JE.pay_date = '$date' AND JH.employee_num = $num AND CL.category = 10 AND JE.active = 'Y')
				WHERE employee_num = $num";   						
		run_statement ($stmnt);
#	print "$stmnt<br/><br/>";

		$stmnt = "UPDATE rpt_daily_employee SET mck = 
				(SELECT (SUM(JE.elapsed))/60 FROM job_events AS JE
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				JOIN clients AS CL ON (CL.client_id = JH.client_id)
				WHERE JE.pay_date = '$date' AND JH.employee_num = $num AND CL.category = 8 AND JE.active = 'Y')
				WHERE employee_num = $num";   						
		run_statement ($stmnt);
#	print "$stmnt<br/><br/>";

		$stmnt = "UPDATE rpt_daily_employee SET off_site = 
				(SELECT PH.oth_min/60 FROM payroll_hrs AS PH 
				WHERE PH.date = '$date' AND PH.employee_num = $num),
				payroll =
				(SELECT PH.hours FROM payroll_hrs AS PH 
				WHERE PH.date = '$date' AND PH.employee_num = $num)
				WHERE employee_num = $num";
		run_statement ($stmnt);
#	print "$stmnt<br/><br/>";

	}
	$stmnt = "UPDATE rpt_daily_employee SET utilization = (client/on_site)*100, on_site_payroll=payroll-off_site";
	run_statement ($stmnt);

	# Now generate the client table
	$stmnt = "INSERT INTO rpt_daily_client (date, client, facility) 
			SELECT '$date', JH.client_id, CL.facility 
			FROM job_events AS JE 
			JOIN job_headers AS JH ON (JH.job_num = JE.job_num)
			JOIN clients AS CL ON (CL.client_id = JH.client_id) 
			WHERE JE.pay_date = '$date' AND JE.active = 'Y' AND CL.facility < 100
			GROUP BY JH.client_id ORDER BY CL.category DESC, JH.client_id";
	run_statement ($stmnt);

	$stmnt = "SELECT client FROM rpt_daily_client ORDER BY facility";
	run_statement ($stmnt);
  	while (($tmp) = $sth->fetchrow) {
		# print "$tmp<br/>";
		push @cliList, $tmp;
	}
	foreach $client (@cliList) {

	$stmnt = "UPDATE rpt_daily_client 
				SET csr = 
				(SELECT (SUM(JE.elapsed))/60 FROM job_events AS JE
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				WHERE JE.pay_date = '$date' AND EM.job = 'CSR' AND JH.client_id = '$client' AND JE.active = 'Y'),
				whs = 
				(SELECT (SUM(JE.elapsed))/60 FROM job_events AS JE
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				WHERE JE.pay_date = '$date' AND EM.job = 'WHS' AND JH.client_id = '$client' AND JE.active = 'Y'),
				tmp = 
				(SELECT (SUM(JE.elapsed))/60 FROM job_events AS JE
				JOIN job_headers AS JH ON (JH.job_num = JE.job_num) 
				JOIN employees AS EM ON (EM.employee_num = JH.employee_num) 
				WHERE JE.pay_date = '$date' AND EM.job = 'TMP' AND JH.client_id = '$client' AND JE.active = 'Y'), 
				std_hrs = 
				(SELECT (SUM(TE.std_time)/60) FROM task_events as TE 
				JOIN job_headers as JH ON (JH.job_num = TE.job_num)
				JOIN job_events as JE ON (JE.job_num = JH.job_num)
				WHERE JE.pay_date = '$date' AND JH.client_id = '$client' AND JE.active = 'Y') 
				WHERE client = '$client'
				";
	# print "$stmnt<br/>";
	run_statement ($stmnt);
	}
	$stmnt = "UPDATE rpt_daily_client SET whs_plus_tmp = whs+tmp, total = csr+whs+tmp, efficiency = (std_hrs/total)*100";
	run_statement ($stmnt);
	$sth->finish;
}


sub start_system_time
{
	my $employee_num = $_[0];
	my $code = $_[1];
	my $time = $_[2];
	my $stmnt = "INSERT INTO sys_time (employee_num, code, start) VALUES ($employee_num, '$code', '$time')";
	run_statement ($stmnt);
#	print "$stmnt<br/>";
	$sth->finish;
}


sub end_system_time
{
	my $employee_num = $_[0];
	my $code = $_[1];
	my $time = $_[2];
	my $stmnt;
	if ( $code eq "ALL" ) {
		$stmnt = "UPDATE sys_time SET end = '$time' WHERE employee_num = $employee_num AND end IS NULL";				
	} else {
		$stmnt = "UPDATE sys_time SET end = '$time' WHERE employee_num = $employee_num AND end IS NULL AND code = 'TASKS'";				
	} 
	run_statement ($stmnt);
	$sth->finish;
}

sub update_system_elapsed_time 
{
	my $employee_num = $_[0];
	my $stmnt = "UPDATE sys_time SET elapsed = TIME_TO_SEC(TIMEDIFF(end,start)) WHERE employee_num = $employee_num AND elapsed IS NULL";
	run_statement ($stmnt);
	$sth->finish;
}

return 1;


